如何從每個組中選擇前5個日期并將它們放在Oracle中以逗號分隔的單個列中？

我有一張這樣的表：

我想撰寫一個按部門和地區列分組的查詢，并在單列中為每個組提供以逗號分隔的最后 5 個日期。像這樣的東西：

最后兩列是我在最終輸出表中也需要的自定義計算列。任何幫助將不勝感激，因為我已經嘗試了很多東西，但我不斷收到關于它沒有正確分組的錯誤，可能是因為最后有兩個額外的列。但即使沒有那個，我也不知道如何在 oracle 中只獲取每組的最后 5 個日期。

謝謝！

uj5u.com熱心網友回復：

您想使用ROW_NUMBER決議函式過濾每個組中最大的 n 個，然后聚合：

SELECT division,
       region,
       LISTAGG(TO_CHAR(date_of_last_visit, 'DD/MM/YYYY'), ',')
         WITHIN GROUP (ORDER BY date_of_last_visit DESC)
         AS date_of_last_visit,
       SYSDATE AS today,
       TRUNC(SYSDATE - MAX(date_of_last_visit)) AS days_since_last_visit
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (PARTITION BY division, region
                            ORDER BY date_of_last_visit DESC) AS rn
  FROM   table_name t
)
WHERE  rn <= 5
GROUP BY
       division,
       region

其中，對于樣本資料：

CREATE TABLE table_name (division, region, date_of_last_visit) as
select 1, 2, date '2021-11-20' from dual union all
select 1, 2, date '2021-11-18' from dual union all
select 1, 7, date '2021-10-18' from dual union all
select 1, 7, date '2021-11-19' from dual union all
select 2, 2, date '2021-11-17' from dual union all
select 2, 3, date '2021-09-20' from dual union all
select 2, 3, date '2021-10-20' from dual;

輸出：

分配	地區	DATE_OF_LAST_VISIT	今天	DAYS_SINCE_LAST_VISIT
1	2	20/11/2021,18/11/2021	21 年 11 月 30 日	10
1	7	19/11/2021,18/10/2021	21 年 11 月 30 日	11
2	2	17/11/2021	21 年 11 月 30 日	13
2	3	20/10/2021,20/09/2021	21 年 11 月 30 日	41

db<>在這里擺弄

uj5u.com熱心網友回復：

干得好; 閱讀代碼中的注釋。

SQL> with test (division, region, datum) as
  2    -- sample data
  3    (select 1, 2, date '2021-11-20' from dual union all
  4     select 1, 2, date '2021-11-18' from dual union all
  5     select 1, 7, date '2021-10-18' from dual union all
  6     select 1, 7, date '2021-11-19' from dual union all
  7     select 2, 2, date '2021-11-17' from dual union all
  8     select 2, 3, date '2021-09-20' from dual union all
  9     select 2, 3, date '2021-10-20' from dual
 10    ),

 11  temp as
 12    -- rank rows per division/region, sorted by date column in descending order
 13    (select t.*,
 14       rank() over (partition by division, region order by datum desc) rnk
 15     from test t
 16    )
 17  -- select up to 5 last rows per division/region
 18  select division, region,
 19    listagg(datum, ', ') within group (order by datum) dates,
 20    trunc(sysdate) today,
 21    --
 22    (select trunc(sysdate) - a.datum
 23     from temp a
 24     where a.division = t.division
 25       and a.region = t.region
 26       and a.rnk = 1) days_since
 27  from temp t
 28  where rnk <= 5
 29  group by division, region
 30  order by division, region;

  DIVISION     REGION DATES                          TODAY      DAYS_SINCE
---------- ---------- ------------------------------ ---------- ----------
         1          2 11/18/2021, 11/20/2021         11/30/2021         10
         1          7 10/18/2021, 11/19/2021         11/30/2021         11
         2          2 11/17/2021                     11/30/2021         13
         2          3 09/20/2021, 10/20/2021         11/30/2021         41

SQL>

標籤：甲骨文 每组最大 n

上一篇：PL/SQL創建程序-階乘

下一篇：Oracle動態查找每個表的行數和列數