oracle 每个分组最多选择最后2个值并并排显示

w41d8nur 于 2023-10-16 发布在 Oracle

关注(0)|答案(1)|浏览(104)

表会定期更新（递增load_id）。如何选择

*对于列名称中的每个值
*列中的最后两个值count

其中last由max（load_id）per day给出
然后把它们并排放
输入

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

输出

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

尝试

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

oracle

来源：https://stackoverflow.com/questions/77105248/select-up-to-last-2-values-per-grouping-and-display-them-side-by-side

1条答案

按热度按时间

7jmck4yq1#

按name和day聚合分组，以找到MAX最大值load_id和相应的count，然后使用ROW_NUMBER对每个人的行进行编号，以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
  )
)

对于样本数据，两者都输出：
| 名称|最后一天|前一天|
| --|--|--|
| 约翰| 9 | 7 |
| 马克| 10 |* 空 |
| 奈德| 0 | 空 *|
| 保罗| 1 | 1 |
要获得相应的load_id s：

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
  )
)

两者输出：
| 名称|最后一天|LAST_DAY_LOAD_ID|前一天|前一天装载ID|
| --|--|--|--|--|
| 约翰| 9 | 14 | 7 | 13 |
| 马克| 10 | 11 |* 空 | 空 |
| 奈德| 0 | 10 | 空 | 空 *|
| 保罗| 1 | 13 | 1 | 11 |
fiddle

赞(0）回复(0）举报 2023-10-16

我来回答

oracle 每个分组最多选择最后2个值并并排显示

1条答案

相关问题

热门标签

最新问答