我想找到占銷售額一定百分比的類別，并根據它們在 SQL 中的百分比對其進行細分。為此，我必須首先按收入降序對它們進行排序，然后選擇前 N%。例如，如果總收入為 20M：
Category Revenue
1 6.000.000
2 4.000.000
3 4.000.000
4 3.000.000
5 1.500.000
6 500.000
7 400.000
8 300.000
9 200.000
10 100.000
Total 20.000.000
- 占收入 70% (1400 萬) 的類別 - A 部分
- 占 15% (3M) 的類別 - B 部分
- 占 10% (2M) 的類別 - C 部分
- 占 5% (1M) 的類別 - D 部分
所以，段應該是這樣的：
Category Segment
1 A
2 A
3 A
4 B
5 C
6 C
7 D
8 D
9 D
10 D
uj5u.com熱心網友回復：
我確信有一種更簡單的方法可以得到這個結果，但是這里有一個 [相當長的] 查詢，它根據您的邏輯對細分中的類別進行分類：
with
q as (
select *,
sum(revenue) over(order by revenue desc) as acc_revenue,
sum(revenue) over() as tot_revenue
from t
),
a as (
select * from q where acc_revenue <= 0.7 * tot_revenue
),
b as (
select *
from q
where acc_revenue - (select max(acc_revenue) from a) <= 0.15 * tot_revenue
and category not in (select category from a)
),
c as (
select *
from q
where acc_revenue - (select max(acc_revenue) from b) <= 0.10 * tot_revenue
and category not in (select category from a)
and category not in (select category from b)
),
d as (
select *
from q
where category not in (select category from a)
and category not in (select category from b)
and category not in (select category from c)
)
select *, 'A' as segment from a
union all select *, 'B' from b
union all select *, 'C' from c
union all select *, 'D' from d
結果：
category revenue acc_revenue tot_revenue segment
--------- -------- ------------ ------------ -------
1 6000000 6000000 20000000 A
2 4000000 14000000 20000000 A
3 4000000 14000000 20000000 A
4 3000000 17000000 20000000 B
5 1500000 18500000 20000000 C
6 500000 19000000 20000000 C
7 400000 19400000 20000000 D
8 300000 19700000 20000000 D
9 200000 19900000 20000000 D
10 100000 20000000 20000000 D
請參閱DB Fiddle上的運行示例。
uj5u.com熱心網友回復：
此答案使用與上一個答案類似的視窗函式，但包含一個 CASE 運算式而不是多個 CTE。
WITH
cte AS (
SELECT category, revenue,
sum(revenue) OVER(ORDER BY revenue DESC, category)*1. /*multiply by 1. (or use CAST) to avoid integer truncation in immediate next step*/
/sum(revenue) OVER() RunTtlPct /*Running total of sales, as a percent of the grand total*/
FROM t)
SELECT category, revenue,
CASE
WHEN RunTtlPct <= 0.7 THEN 'A'
WHEN RunTtlPct <= 0.85 THEN 'B'
WHEN RunTtlPct <= 0.95 THEN 'C'
ELSE 'D'
END Segment
FROM cte /*cte was included to avoid repeating RunTtlPct's expression in every WHEN clause.*/
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/425364.html
標籤：sql PostgreSQL tsql 亚马逊红移
上一篇：使用從select陳述句中提取的資料庫名稱的SQL陳述句
下一篇：識別隨時間的變化