檢查另一列中標準上每一行的值是否相同-有解無憂

資料庫小提琴

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');

預期結果：

campaign  |   supplier   |   plan_quantity   |     check      |
----------|--------------|-------------------|----------------|-------
  C001    |  supplier_a  |        500        |      same      |
  C001    |  supplier_a  |        500        |      same      |
  C001    |  supplier_b  |        500        |      same      |
----------|--------------|-------------------|----------------|-------
  C002    |  supplier_a  |        600        |    different   |
  C002    |  supplier_b  |        700        |    different   | 
----------|--------------|-------------------|----------------|-------
  C003    |  supplier_c  |        100        |      same      |
  C003    |  supplier_c  |        100        |      same      |
----------|--------------|-------------------|----------------|-------
  C004    |  supplier_a  |        900        |    different   |
  C004    |  supplier_c  |        800        |    different   |
  C004    |  supplier_d  |        250        |    different   |
  C004    |  supplier_d  |        250        |    different   |

在check結果的列中，我想查看plan_quantity每個supplierper 的值campaign是否相同。
如果是那么應該寫same如果不是應該寫different。

SELECT
campaign AS campaign,
supplier AS supplier,
plan_quantity AS plan_quantity,
(CASE WHEN plan_quantity for each supplier per campaign is the same THEN 'same' else 'different' END) AS check
FROM campaigns
ORDER BY 1,2,3;

我不知道我需要什么功能才能使它作業。
你有什么主意嗎？

uj5u.com熱心網友回復：

您可以使用視窗分析函式來檢查值：

select *, 
  case when 
      Min(plan_quantity) over(partition by campaign) 
        = Max(plan_quantity) over(partition by campaign) 
    then 'same' else 'different'
    end as "Check"
from campaigns

uj5u.com熱心網友回復：

您可以使用視窗max和min

SELECT
  campaign AS campaign,
  supplier AS supplier,
  plan_quantity AS plan_quantity,
  CASE WHEN max(plan_quantity) over(partition by supplier, campaign) = min(plan_quantity) over(partition by supplier, campaign) THEN 'same' ELSE 'different' END AS check
FROM campaigns
ORDER BY 1,2,3;

uj5u.com熱心網友回復：

您可以通過分組并簡單地計算 distint來找出哪個campaign具有唯一性plan_quantitycampaignplan_quantity

SELECT
    campaign AS campaign
FROM campaigns
GROUP BY campaign
HAVING COUNT(DISTINCT plan_quantity) = 1

有了它，你可以做你想做的。以連接為例：

SELECT
    campaigns.campaign,
    supplier,
    plan_quantity,
    CASE WHEN grouping IS NOT NULL THEN 'same' ELSE 'different' END as check
FROM campaigns
    LEFT JOIN (
        -- Get all campaign with same plan_quantity
        SELECT
            campaign
        FROM campaigns
        GROUP BY campaign
        HAVING COUNT(DISTINCT plan_quantity) = 1
    ) as grouping
ON campaigns.campaign = grouping.campaign

轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/399505.html

標籤：sql PostgreSQL的

上一篇：SQL查詢以遞回方式獲取經理下所有員工的姓名

下一篇：如何使用ROOM格式化查詢