我有一張具有這種格式的表格
id col1 avg_amount_per_order
1 a 80
1 b 50
2 a 90
2 b 120
3 a 200
3 b 140
4 a 110
4 b 430
我要選擇所有的資料，只要 if col1 = a，那么avg_amount_per_order必須be >= 100。對于相同的 id，我只想選擇行，其中avg_amount_per_orderof'b'至少2* avg_amount_per_order為'a'in col1。
怎么做？
我試過這個：
SELECT
id
, col1
, avg_amount_per_order
from orders
WHERE
nr_orders >= 6
AND CASE WHEN col1 = 'a' THEN avg_amount_per_order >= 100 ELSE avg_amount_per_order > 0 END
group by
1,2,3
但這不會回傳預期的結果。
uj5u.com熱心網友回復：
這可能是為您的問題找到解決方案的簡單方法。
with tbl(id, col1, avg_amount_per_order) as (
values
(1, 'a', 80 ),
(1, 'b', 50 ),
(2, 'a', 90 ),
(2, 'b', 120),
(3, 'a', 200),
(3, 'b', 140),
(4, 'a', 110),
(4, 'b', 430)
)
select a.*
from tbl a
join tbl b on a.id = b.id and a.col1 != b.col1
WHERE (a.avg_amount_per_order >= b.avg_amount_per_order * 2
and b.col1 = 'a' and b.avg_amount_per_order >= 100)
or a.avg_amount_per_order *2 <= b.avg_amount_per_order
uj5u.com熱心網友回復：
使用LEAD窗函式來獲取價值觀和a和b在一起。然后對 的外部查詢b>= 2*a：
select id, avg_amount_per_order_a, avg_amount_per_order_b
from ( select id
, avg_amount_per_order avg_amount_per_order_a
, lead(avg_amount_per_order) over (partition by id order by col1) avg_amount_per_order_b
from testa
) ab
where avg_amount_per_order_b >= 2 * avg_amount_per_order_a;
uj5u.com熱心網友回復：
with data as (
select *,
min(avg_amount_per_order) over (partition by id) filter (col1 = 'a') as avgA
min(avg_amount_per_order) over (partition by id) filter (col1 = 'b') as avgB
from T
)
select * from data
where avgA >= 100 and avgB >= 2 * avgA
-- ?? where col1 = 'a' and avgA >= 100 or col1 = 'b' and avgB >= 2 * avgA
我不知道你是只想要一個條件還是兩者都想要。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/384194.html
標籤：sql PostgreSQL的 数据分析