使用超過磁區條件比較兩個表之間的值

DB-小提琴

/* Table Campaigns */
CREATE TABLE campaigns (
    id SERIAL PRIMARY KEY,
    insert_time DATE,
    campaign VARCHAR,
    tranches VARCHAR,
    quantity DECIMAL);

INSERT INTO campaigns
(insert_time, campaign, tranches, quantity)
VALUES 
('2021-01-01', 'C001', 't', '500'),
('2021-01-01', 'C002', 't', '600'),

('2021-01-02', 'C001', 't', '500'),
('2021-01-02', 'C002', 't', '600');


/* Table Tranches */
CREATE TABLE tranches (
    id SERIAL PRIMARY KEY,
    insert_time DATE,
    campaign VARCHAR,
    tranches VARCHAR,
    quantity DECIMAL);

INSERT INTO tranches
(insert_time, campaign, tranches, quantity)
VALUES 
('2021-01-01', 'C001', 't1', '200'),
('2021-01-01', 'C001', 't2', '120'),
('2021-01-01', 'C001', 't3', '180'),

('2021-01-01','C002', 't1', '350'),
('2021-01-01','C002', 't2', '250'),

('2021-01-02', 'C001', 't1', '400'),
('2021-01-02', 'C001', 't2', '120'),
('2021-01-02', 'C001', 't3', '180'),

('2021-01-02','C002', 't1', '350'),
('2021-01-02','C002', 't2', '250');

預期結果：

insert_time   |  campaign  |  tranches  |  quantity_campaigns |  quantity_tranches  |    check
--------------|------------|------------|---------------------|---------------------|-----------
2021-01-01    |    C001    |      t     |          500        |         500         |    ok
2021-01-01    |    C002    |      t     |          600        |         600         |    ok
--------------|------------|------------|---------------------|---------------------|------------
2021-01-02    |    C001    |      t     |          500        |         700         |    error   
2021-01-02    |    C002    |      t     |          600        |         500         |    ok

我想將表中每個廣告系列的總數量與表中每個廣告系列的總數量進行比較。campaignstranches

到目前為止，我已經能夠開發這個查詢：

SELECT
c.insert_time AS insert_time,
c.campaign AS campaign,
c.tranches AS tranches,
c.quantity AS quantity_campaigns,
t.quantity AS quantity_tranches,

(CASE WHEN 
MAX(c.quantity) OVER(PARTITION BY c.insert_time, c.campaign) = SUM(t.quantity) OVER(PARTITION BY t.insert_time, t.campaign) 
THEN 'ok' ELSE 'error' END) AS check

FROM campaigns c
LEFT JOIN tranches t ON c.campaign = t.campaign
ORDER BY 1,2,3,4,5;

但是，它沒有給我預期的結果嗎？
我需要更改什么才能使其正常作業？

uj5u.com熱心網友回復：

我認為你正在尋找的結果應該是這樣的。問題是您試圖在連接后聚合兩個分組，這將產生太多結果或不正確的計算。通過在 CTE 中進行聚合，然后在聚合發生后加入 CTE，您可以獲得所需的結果。請參閱下面的示例：

WITH campaign_agg AS(
  SELECT c.insert_time, c.campaign, c.tranches, MAX(c.quantity) c_quantity
  FROM campaigns c
  GROUP BY c.insert_time, c.campaign, c.tranches
), tranch_agg AS(
  SELECT t.insert_time, t.campaign, SUM(t.quantity) as t_sum
  FROM tranches t
  GROUP BY t.insert_time, t.campaign
)
SELECT c.insert_time, c.campaign, c.tranches, c.c_quantity, t.t_sum,
    CASE WHEN c.c_quantity = t.t_sum THEN 'ok' ELSE 'error' END as check
FROM campaign_agg c
JOIN
    tranch_agg t ON 
        t.insert_time = c.insert_time
        AND t.campaign = c.campaign
ORDER BY c.insert_time, c.campaign

我也有一個 db-fiddle：https ://www.db-fiddle.com/f/33x4upVEcgTMNehiHCKzfN/1

uj5u.com熱心網友回復：

DB-小提琴

SELECT
c.insert_time AS insert_time, 
c.campaign AS campaign, 
c.tranches AS tranches, 
SUM(c.quantity) AS quantity_campaigns,
SUM(t1.quantity) AS quantity_tranches,
(CASE WHEN SUM(c.quantity) <> SUM(t1.quantity) THEN 'error' ELSE 'ok' END) AS check
FROM campaigns c
LEFT JOIN

   (SELECT
   t.insert_time AS insert_time,
   t.campaign AS campaign,
   SUM(t.quantity) AS quantity
   FROM tranches t
   GROUP BY 1,2
   ORDER BY 1,2) t1 on t1.insert_time = c.insert_time AND t1.campaign = c.campaign
   
GROUP BY 1,2,3
ORDER BY 1,2,3;

標籤：

上一篇：POSTGRESQL-給定月份同一客戶的所有訂單的總和

下一篇：另一個查詢中的SQL查詢？