資料庫小提琴
CREATE TABLE campaigns
(
id SERIAL PRIMARY KEY,
campaign VARCHAR,
supplier VARCHAR,
plan_quantity DECIMAL
);
INSERT INTO campaigns (campaign, supplier, plan_quantity)
VALUES ('C001', 'supplier_a', '500'),
('C001', 'supplier_a', '500'),
('C001', 'supplier_b', '500'),
('C002', 'supplier_a', '600'),
('C002', 'supplier_b', '700'),
('C003', 'supplier_c', '100'),
('C003', 'supplier_c', '100'),
('C004', 'supplier_a', '900'),
('C004', 'supplier_c', '800'),
('C004', 'supplier_d', '250'),
('C004', 'supplier_d', '250'),
('C005', 'supplier_b', '380'),
('C005', 'supplier_b', '270'),
('C005', 'supplier_d', '590');
預期結果：
campaign | supplier | plan_quantity | check |
----------|--------------|-------------------|--------------------|-------
C001 | supplier_a | 500 | same |
C001 | supplier_a | 500 | same |
C001 | supplier_b | 500 | non-relevant |
----------|--------------|-------------------|--------------------|-------
C002 | supplier_a | 600 | non-relevant |
C002 | supplier_b | 700 | non-relevant |
----------|--------------|-------------------|--------------------|-------
C003 | supplier_c | 100 | same |
C003 | supplier_c | 100 | same |
----------|--------------|-------------------|--------------------|-------
C004 | supplier_a | 900 | non-relevant |
C004 | supplier_c | 800 | non-relevant |
C004 | supplier_d | 250 | same |
C004 | supplier_d | 250 | same |
----------|--------------|-------------------|--------------------|-------
C005 | supplier_b | 380 | different |
C005 | supplier_b | 270 | different |
C005 | supplier_d | 590 | non-relevant |
如果供應商在每個活動中出現多次，我想在列check中plan_quantity查看該供應商的每行是否相同。
當我從這個問題修改查詢時，我接近結果：
SELECT
campaign AS campaign,
supplier AS supplier,
plan_quantity AS plan_quantity,
(CASE
WHEN MIN(plan_quantity) OVER (PARTITION BY supplier, campaign) = MAX(plan_quantity) OVER (PARTITION BY supplier, campaign)
THEN 'same'
ELSE 'different'
END) AS check
FROM
campaigns
ORDER BY
1, 2, 3;
但是，我不知道如何添加與查詢無關的描述，以防供應商沒有在每個活動中多次出現。
你有什么主意嗎？
uj5u.com熱心網友回復：
當檢查相同時，您可以使用額外的case 運算式稍微修改查詢，并指示任何計數為 1 的都是不相關的：
select
campaign,
supplier,
plan_quantity,
case when
Min(plan_quantity) over(partition by campaign, supplier)
= Max(plan_quantity) over(partition by campaign, supplier)
then
case when Count(*) over(partition by campaign, supplier) = 1
then 'non-relevant' else 'same'
end
else 'different'
end as Check
from campaigns
order by 1,2,3;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/399499.html
標籤：sql PostgreSQL的