使用 SQL 根据列表中的顺序 Select 值

发布于06月13日

我的数据如下:

USER_ID value  date         cost
001         A  01-01-2021     10
002         C  01-01-2021     11
001         D  01-02-2021     21
002         G  01-02-2021     23
001         H  01-03-2021     32

我想做的就是 for each 用户确定哪value个发生在第二位，哪value个发生在第三位.这可由date确定.拿到这个之后，我们还需要那些日子的费用.

结果表应如下所示:

USER_ID   second_val  third_val second_cost  third_cost
001                D          H          21          32
002                G         NA          23           0

请注意，USER_ID 002没有第三个值，这将被视为NA.因此，NA val的成本为0.

推荐答案

这应该足够了:

CREATE OR REPLACE TABLE TEMP1 
AS 
SELECT USER_ID, value,  date, cost
  FROM (VALUES 
        ('001', 'A', '01-01-2021', 10),
        ('002', 'C', '01-01-2021', 11),
        ('001', 'D', '01-02-2021', 21),
        ('002', 'G', '01-02-2021', 23),
        ('001', 'H', '01-03-2021', 32)) t(USER_ID, value, date, cost);
               
SELECT DISTINCT USER_ID
     , IFNULL(NTH_VALUE(value, 2) OVER(PARTITION BY USER_ID ORDER BY date), 'NA') AS second_val
     , IFNULL(NTH_VALUE(value, 3) OVER(PARTITION BY USER_ID ORDER BY date), 'NA') AS third_val
     , IFNULL(NTH_VALUE(cost, 2) OVER(PARTITION BY USER_ID ORDER BY date), 0) AS second_cost
     , IFNULL(NTH_VALUE(cost, 3) OVER(PARTITION BY USER_ID ORDER BY date), 0) AS third_cost
  FROM TEMP1;