sql - 用于查找前两条记录和最后一条记录的特殊 SQL 条件查询？

Question

我有一个像这样的数据集:

 student_id     date     project_id
 1              1/1/18   15
 1              1/1/18   17
 1              2/2/18   16 
 1              3/3/18   15 
 1              3/3/18   12 
 2              2/3/18   3
 2              4/3/18   4
 2              5/3/18   6 
 2              5/3/18   4 

我想用他们做的前两个项目和他们做的最后一个项目找到学生 ID，

student_id    project_id1st    date1st     project_id2nd      date2nd projectlast    datelast
1             15               1/1/18      17                 1/1/18  12               3/3/18
2             3                2/3/18      4                  4/3/18  4                5/3/18

首先我想在 pandas 中解决它，但得到了一些不好的结果。然后我尝试用SQL解决它。

WITH abc AS (
  SELECT student_id, project_id, date, 
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) rn
  FROM table 
)
SELECT student_id, 
       SUM(CASE WHEN rn = 1 THEN abc.project_id END) as firstid,
       SUM(CASE WHEN rn = 2 THEN abc.project_id END) as secondsid,
       MIN(CASE WHEN rn = 1 THEN date END) as first,
       MIN(CASE WHEN rn = 2 THEN date END) as second
FROM abc
GROUP BY 1;

我得到了不错的结果，但不知何故，它使用 ROW_NUMBER() 弄乱了数据集的顺序。例如对于学生1而言，rownumber值为1的project_id 17，project_id 15将成为第二个开始日期。

Answer 1

您的问题是您没有指示正确顺序的列。在 row_number 窗口框架中，您输入一个order by date。但是当帧中有很多行时，结果是完全随机的。

您必须告诉数据库如何处理同一帧中的行。您要使用 project_id 吗？没有“原始订单”这样的指标。

例如

SELECT * FROM table

从不给出专门的顺序(例如，插入数据集的顺序)。结果集可以完全随机排序。对于您定义的任何框架窗口也是如此。

因此您需要一种方法(列或算法)来确保您期望的顺序。

---

如果您采用 ORDER BY date, project_id 那么(例如)3/3/18 的框架将使用 project_id 12, 15 这不是您的“原始订单”。如果您要订购它 DESC，您的 1/1/18 框架订购错误，因为第一个 ID 将是 17。所以 project_id 不是一个好的顺序标准。但没有其他可能的订购方式。那是因为您需要另一列。

要让您的“原始订单”自动递增 ID 列(键入 serial - 或者在 Postgres 10 和更高版本的情况下 GENERATED AS IDENTITY - 会有所帮助)。

---

当您有特定顺序(例如插入 ID 列)时，这可能是您的查询:

WITH abc AS (
    SELECT insert_id, student_id, project_id, date, 
        ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY insert_id) rn_asc,        -- A
        ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY insert_id DESC) rn_desc   -- B
    FROM projects
)
SELECT 
    student_id,
    MAX(project_id) FILTER (WHERE rn_asc = 1),
    MAX(date) FILTER (WHERE rn_asc = 1),
    MAX(project_id) FILTER (WHERE rn_asc = 2),
    MAX(date) FILTER (WHERE rn_asc = 2),
    MAX(project_id) FILTER (WHERE rn_desc = 1),
    MAX(date) FILTER (WHERE rn_desc = 1)
FROM abc
GROUP BY student_id

A:按 ID 升序排列学生帧，并给出行号 1 和 2，这有助于过滤前两行。

B:按相同的降序顺序获取最后一行(在本例中获取行号== 1)

demo: db<>fiddle