Sql 每个分组最多 Select 最后 2 个值并并排显示它们

发布于09月14日

定期更新表(递增Load_id).如何 Select

列name中的for each值
the last列count中的两个值
其中，last是max(load_id) per day给出的
然后把它们放在side by side块？

Input个

name,day,count,load_id
John,10,9,14
John,9,5,12
John,9,7,13
John,8,5,12
John,8,2,11
Paul,9,0,12
Paul,9,1,13
Paul,7,1,11
Paul,7,9,10
Mark,7,10,11
Mark,7,11,10
Ned,7,0,10

步骤1-每天最后一次记录

name,day,count
John,10,9
John,9,7
John,8,5
Paul,9,1
Paul,7,1
Mark,7,10
Ned,7,0

Output个

name,last_day_last_count,prev_day_last_count
John,9,7
Paul,1,1
Mark,10,NULL
Ned,0,NULL

Attempt个

WITH RankedData AS (
  SELECT
    name,
    day,
    count,
    load_id,
    ROW_NUMBER() OVER (PARTITION BY name, day ORDER BY load_id DESC) AS rn
  FROM test
)

SELECT *
FROM RankedData
WHERE rn = 1
ORDER BY name, day DESC

Fiddle个

推荐答案

按name和day进行聚合分组，以找到MAX最小值load_id和相应的count，然后使用ROW_NUMBER for each 人按DESC结束load_id对行进行编号，最后使用PIVOT获得最新的2个值作为列，而不是行:

SELECT *
FROM   (
  SELECT name,
         MAX(count) KEEP (DENSE_RANK LAST ORDER BY load_id) AS count,
         ROW_NUMBER() OVER (PARTITION BY name ORDER BY MAX(load_id) DESC) AS rn
  FROM   test
  GROUP BY name, day
)
PIVOT(
  MAX(count) FOR rn IN (
    1 AS last_day_count,
    2 AS prev_day_count
  )
)

或者在两次传递中使用ROW_NUMBER:

SELECT *
FROM   (
  SELECT name,
         count,
         ROW_NUMBER() OVER (PARTITION BY name ORDER BY load_id DESC)
           AS rn_by_person
  FROM   (
    SELECT name,
           load_id,
           count,
           ROW_NUMBER() OVER (PARTITION BY name, day ORDER BY load_id DESC)
             AS rn_by_day
    FROM   test
  )
  WHERE  rn_by_day = 1
)
PIVOT(
  MAX(count) FOR rn_by_person IN (
    1 AS last_day_count,
    2 AS prev_day_count
  )
)

对于样本数据，这两个输出都是:

NAME	LAST_DAY_COUNT	PREV_DAY_COUNT
John	9	7
Mark	10	null
Ned	0	null
Paul	1	1

要获得相应的load_id分，请执行以下操作:

SELECT *
FROM   (
  SELECT name,
         MAX(count) KEEP (DENSE_RANK LAST ORDER BY load_id) AS count,
         MAX(load_id) AS load_id,
         ROW_NUMBER() OVER (PARTITION BY name ORDER BY MAX(load_id) DESC) AS rn
  FROM   test
  GROUP BY name, day
)
PIVOT(
  MAX(count) AS count,
  MAX(load_id) AS load_id
  FOR rn IN (
    1 AS last_day,
    2 AS prev_day
  )
)

或

SELECT *
FROM   (
  SELECT name,
         load_id,
         count,
         ROW_NUMBER() OVER (PARTITION BY name ORDER BY load_id DESC)
           AS rn_by_person
  FROM   (
    SELECT name,
           load_id,
           count,
           ROW_NUMBER() OVER (PARTITION BY name, day ORDER BY load_id DESC)
             AS rn_by_day
    FROM   test
  )
  WHERE  rn_by_day = 1
)
PIVOT(
  MAX(count) AS count,
  MAX(load_id) AS load_id
  FOR rn_by_person IN (
    1 AS last_day,
    2 AS prev_day
  )
)

这两项输出都是: