我有兩張這樣的桌子
交易表 CustID | fk 到客戶表， 金額| 每筆交易花費的金額
客戶表 CustID | pk, 年齡| 客戶年齡
我需要進行查詢以按年齡范圍對客戶進行分組或分桶，并且我需要使每個年齡組的前 3 名金額最高，以及到目前為止我所做的最低 3 金額或最低消費金額是 https://dbfiddle。英國/?rdbms=oracle_11.2&fiddle=ccf988ec525dc133cf7825ba052e8054
到目前為止我管理的 內部查詢
我曾嘗試使用 CTE，但仍然不確定如何使用cte獲得每組的前 3 名/每組的 后 3 名
問題出在第一行的第一張圖片上，回報超過 3/他們附加所有記錄，而我只需要前 3 個
感謝您的幫助
uj5u.com熱心網友回復：
您可以使用ROW_NUMBER()分析函式找到最高和最低，然后您可以使用條件聚合LISTAGG在其相應串列中僅顯示最高和最低：
select age_bucket,
LISTAGG(CASE WHEN max_rn <= 3 THEN amount END, ',')
WITHIN GROUP (ORDER BY amount DESC) AS TOP_3_AMOUNT,
LISTAGG(CASE WHEN min_rn <= 3 THEN amount END, ',')
WITHIN GROUP (ORDER BY amount ASC ) AS BOTTOM_3_AMOUNT
FROM (
SELECT amount,
age_bucket,
ROW_NUMBER() OVER (PARTITION BY age_bucket ORDER BY amount DESC) AS max_rn,
ROW_NUMBER() OVER (PARTITION BY age_bucket ORDER BY amount ASC ) AS min_rn
FROM (
select t.amount,
case
when c.age <= 20 THEN '<= 20'
when c.age between 21 and 30 THEN '21-30'
when c.age between 31 and 40 THEN '31-40'
when c.age between 41 and 50 THEN '41-50'
when c.age >= 51 THEN '>= 51'
END as age_bucket
from transaction t
join customer c on t.cust_id=c.cust_id
)
)
WHERE max_rn <= 3
OR min_rn <= 3
group by age_bucket
order by age_bucket asc;
db<>在這里擺弄
我需要將其設定為僅由客戶 ID 區分的前 3 名或后 3 名
select age_bucket,
LISTAGG(CASE WHEN max_rn <= 3 THEN amount END, ',')
WITHIN GROUP (ORDER BY amount DESC) AS TOP_3_AMOUNT,
LISTAGG(CASE WHEN min_rn <= 3 THEN amount END, ',')
WITHIN GROUP (ORDER BY amount ASC ) AS BOTTOM_3_AMOUNT
FROM (
SELECT amount,
age_bucket,
CASE max_cust_rn
WHEN 1
THEN ROW_NUMBER() OVER (PARTITION BY age_bucket ORDER BY amount DESC)
END AS max_rn,
CASE min_cust_rn
WHEN 1
THEN ROW_NUMBER() OVER (PARTITION BY age_bucket ORDER BY amount ASC )
END AS min_rn
FROM (
SELECT amount,
age_bucket,
ROW_NUMBER() OVER (
PARTITION BY age_bucket, cust_id ORDER BY amount DESC) AS max_cust_rn,
ROW_NUMBER() OVER (
PARTITION BY age_bucket, cust_id ORDER BY amount ASC ) AS min_cust_rn
FROM (
select t.amount,
t.cust_id,
case
when c.age <= 20 THEN '<= 20'
when c.age between 21 and 30 THEN '21-30'
when c.age between 31 and 40 THEN '31-40'
when c.age between 41 and 50 THEN '41-50'
when c.age >= 51 THEN '>= 51'
END as age_bucket
from transaction t
join customer c on t.cust_id=c.cust_id
)
)
WHERE max_cust_rn = 1
OR min_cust_rn = 1
)
WHERE max_rn <= 3
OR min_rn <= 3
group by age_bucket
order by age_bucket asc;
db<>在這里擺弄
uj5u.com熱心網友回復：
因為沒有表的描述，我只是試著改了一點，你可以試試：
SELECT age_bucket,
Listagg(
CASE
WHEN seqnumbyeachcusttop = 1 THEN amount
END, ',') within GROUP (ORDER BY amount DESC) AS test_top_3_amouny,
listagg(amount, ',') within GROUP (ORDER BY amount DESC) AS top_3_principal
FROM (
SELECT age_bucket,
seqnumbyeachcusttop,
amount,
row_number() OVER (partition BY amount ORDER BY amount DESC) rn
FROM (
SELECT t.amount AS amount,
t.cust_id AS cust_id,
row_number() OVER (partition BY t.cust_id ORDER BY t.amount DESC) AS seqnumbyeachcusttop,
CASE
WHEN c.age <= 20 THEN ' <= 20'
WHEN c.age BETWEEN 21 AND 30 THEN '21-30'
WHEN c.age BETWEEN 31 AND 40 THEN '31-40'
WHEN c.age BETWEEN 41 AND 50 THEN '41-50'
WHEN c.age >= 51 THEN '>= 51'
END AS age_bucket
FROM TRANSACTION t
JOIN customer c
ON t.cust_id=c.cust_id ))
WHERE rn <= 3) GROUP BY age_bucket ORDER BY age_bucket ASC;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/481648.html
下一篇：出現重復的INSERTINTOTABLEVALUES，跳過該特定插入而不會出錯，然后執行下一個INSERT陳述句