基于联接的高级查询

Question

我的架构如下所示：

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));


create table ads(
  ad_id int,
  ad_name varchar(10)
);

create table ads_insight(
   id int,
   ad_id int,
   date date, 
   ad_clicks int
 );
create table products(
    id int,
    name varchar(10)
);
create table products_insight(
    id int,
    product_id int, 
    sale int, 
    date date
);
create table ads_products(
    ad_id int,
    product_id int
);
                                    

                                    
insert into ads(ad_id, ad_name) values
  (1,'ad1'),
  (2,'ad2'),
  (3,'ad3');
 
 
insert into ads_insight(id, ad_id, date, ad_clicks) values
    (1, 1, '2021-04-25', 1),
    (3, 1, '2021-04-23', 2),
    (4, 1, '2021-04-22', 8),
    (5, 2, '2021-04-25', 6),
    (6, 2, '2021-03-03', 7);


insert into products(id, name) values
    (1,'prod1'),
    (2,'prod2'),
    (3,'prod3'),
    (4,'prod4'),
    (5,'prod5');
                                        
insert into ads_products (ad_id, product_id) values 
    (1, 1),
    (1, 2),
    (2, 3),
    (2, 4),
    (2, 2),
    (3, 1);
insert into products_insight(id, product_id, sale, date) values
    (1, 1, 10, '2021-04-25'),
    (2, 1, 13, '2021-04-24'),
    (3, 1, 15, '2021-04-23'),
    (4, 1, 14, '2021-04-22'),
    (5, 1, 17, '2021-04-21'),
    (6, 1, 15, '2021-04-20'),
    (7, 1, 13, '2021-04-19'),
    (8, 2, 15, '2021-04-25');

这是你的小提琴

图式的快速解释：我有广告：

1. 每个广告都有洞察力，这些洞察力告诉我们某个广告何时处于活动状态(=>ad_clicks必须>0)。
2. 每个ad都有产品(MANY2MONE-ADS_Products表）。每个产品都有products_insight，它告诉我们该产品在某一天产生了多少销售额。

现在，我想获取时间范围2021-04-20-2021-04-25的所有广告，这些广告的ad_clicks>0（我已经做了），并计算每个广告在活动时产生了多少销售额。因此，只有当广告的ad_insight和ad_clicks大于0时，才计算销售。

我的查询如下所示：

SET @from_date = '2021-04-20';
SET @to_date = '2021-04-25';

SELECT 
    ads.ad_name, 
    IFNULL(ad_clicks, 0) AS clicks, 
    IFNULL(product_sale, 0) AS product_sale, 
    IFNULL(products, '') AS products
FROM ads
LEFT JOIN (
    SELECT ad_id, SUM(ad_clicks) AS ad_clicks
    FROM ads_insight
    WHERE date BETWEEN @from_date AND @to_date
    GROUP BY ad_id
) AS ai ON ai.ad_id = ads.ad_id
LEFT JOIN (
    SELECT ad_id, SUM(sale) AS product_sale
    FROM ads_products AS ap
    LEFT JOIN products_insight AS pi ON pi.product_id = ap.product_id
    WHERE date BETWEEN @from_date AND @to_date
    GROUP BY ad_id
) AS pi ON pi.ad_id = ads.ad_id
LEFT JOIN (
    SELECT ap.ad_id, GROUP_CONCAT(DISTINCT p.name) AS products
    FROM ads_products AS ap 
    JOIN products AS p ON ap.product_id = p.id
    GROUP BY ap.ad_id
) AS p ON ads.ad_id = p.ad_id 
WHERE ad_clicks>0;

并生成以下结果：

| ad_name | clicks | product_sale | products          |
| ------- | ------ | ------------ | ----------------- |
| ad1     | 11     | 99           | prod1,prod2       |
| ad2     | 6      | 15           | prod2,prod3,prod4 |

但我希望这样（在PRODUCT_SALEL列中有区别）

| ad_name | clicks | product_sale | products          |
| ------- | ------ | ------------ | ----------------- |
| ad1     | 11     | 54           | prod1,prod2       |
| ad2     | 6      | 15           | prod2,prod3,prod4 |

54因为它只计算products_insight中id为1,3,4的行，因为在这些日子里id为1的ad处于活动状态。（活动表示ads_insight表中有一行。

Answer 1

您可以在pi子查询中添加相同的日期conition。将有效地使其内部连接

..
    (SELECT ap.ad_id, sum(pi.sale) AS product_sale
    FROM ads_products AS ap
    JOIN ads_insight AS ai ON ai.ad_id = ap.ad_id
        AND date BETWEEN @from_date AND @to_date
    JOIN products_insight AS pi ON pi.product_id = ap.product_id AND pi.date = ai.date 
    GROUP BY ap.ad_id) as pi ..

整个查询，其余的都是从原来的一个

SET @from_date = '2021-04-20';
SET @to_date = '2021-04-25';

SELECT 
    ads.ad_name, 
    IFNULL(ad_clicks, 0) AS clicks, 
    IFNULL(product_sale, 0) AS product_sale, 
    IFNULL(products, '') AS products
FROM ads
LEFT JOIN (
    SELECT ad_id, SUM(ad_clicks) AS ad_clicks
    FROM ads_insight
    WHERE date BETWEEN @from_date AND @to_date
    GROUP BY ad_id
) AS ai ON ai.ad_id = ads.ad_id
LEFT JOIN (
    SELECT ap.ad_id, sum(pi.sale) AS product_sale
    FROM ads_products AS ap
    JOIN ads_insight AS ai ON ai.ad_id = ap.ad_id
        AND date BETWEEN @from_date AND @to_date
    JOIN products_insight AS pi ON pi.product_id = ap.product_id AND pi.date = ai.date 
    GROUP BY ap.ad_id
) AS pi ON pi.ad_id = ads.ad_id
LEFT JOIN (
    SELECT ap.ad_id, GROUP_CONCAT(DISTINCT p.name) AS products
    FROM ads_products AS ap 
    JOIN products AS p ON ap.product_id = p.id
    GROUP BY ap.ad_id
) AS p ON ads.ad_id = p.ad_id 
WHERE ad_clicks>0;

小提琴