当前位置: 首页 > 知识库问答 >
问题:

高级表上的汇总行

臧兴学
2023-03-14

我的sql表如下所示:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
create table ad(
  ad_id int,
  ad_name varchar(10)
);

create table ad_insight(
   id int,
   ad_id int,
   date date, 
   clicks int
 );
create table product(
    product_id int,
    product_name varchar(10)
);
create table product_insight(
    id int,
    product_id int, 
    sale int, 
    date date
);
create table ads_products(
    ad_id int,
    product_id int
);                              
                                    
insert into ad(ad_id, ad_name) values
  (1,'ad1'),
  (2,'ad2'),
  (3,'ad3');
 
 
insert into ad_insight(id, ad_id, date, clicks) values
    (1, 1, '2021-04-25', 1),
    (2, 1, '2021-04-24', 4),
    (3, 1, '2021-04-23', 2),
    (4, 2, '2021-04-25', 6),
    (5, 2, '2021-03-03', 7);


insert into product(product_id, product_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),
    (3, 1); 
insert into product_insight(id, product_id, sale, date) values
    (1, 1, 12, '2021-04-25'),
    (2, 1, 11, '2021-04-24'),
    (3, 1, 13, '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, 19, '2021-04-25');
                                    

这是你的小提琴

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

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

要获得下表,该表将从ad_insight表中的单击进行汇总,并将从2021-04-23中的product_insight中的product_sale汇总到2021-04-25(包括在内)。

+----------+--------+--------------+--------------+
| ad_name  | clicks | product_sale |   products   |
+----------+--------+--------------+--------------+
| ad1      |      7 |           55 | prod1, prod2 |
| ad2      |      6 |            0 | prod3, prod4 |
| ad3      |      0 |           36 | prod1        |
+----------+--------+--------------+--------------+

此表由以下查询解决:

SELECT ad.ad_name, IFNULL(clicks, 0) AS clicks, IFNULL(product_sale, 0) AS product_sale, IFNULL(GROUP_CONCAT(DISTINCT p.product_name), '') AS products
FROM ad
LEFT JOIN (
    SELECT ad_id, SUM(clicks) AS clicks
    FROM ad_insight
    WHERE date BETWEEN '2021-04-23' AND '2021-04-25'
    GROUP BY ad_id
) AS ai ON ai.ad_id = ad.ad_id
LEFT JOIN (
    SELECT ad_id, SUM(sale) AS product_sale
    FROM ads_products AS ap
    LEFT JOIN product_insight AS pi ON pi.product_id = ap.product_id
    WHERE date BETWEEN '2021-04-23' AND '2021-04-25'
    GROUP BY ad_id
) AS pi ON pi.ad_id = ad.ad_id
LEFT JOIN ads_products AS ap ON ap.ad_id = ad.ad_id
LEFT JOIN product AS p ON ap.product_id = p.product_id
GROUP BY ad.ad_id;

但现在我想要一个摘要行,它将如下所示:

+-----------+--------------+-----------------------------+-------------------------+
| total_ads | total_clicks | distinct_total_product_sale | disctinct_all_products  |
+-----------+--------------+-----------------------------+-------------------------+
|         3 |           13 |                          55 | prod1,prod2,prod3,prod4 |
+-----------+--------------+-----------------------------+-------------------------+

我试过这样的东西:

SELECT count(*), SUM(clicks), SUM(product_sale), IFNULL(GROUP_CONCAT(DISTINCT products), '') FROM 
    (SELECT ad.ad_name, IFNULL(clicks, 0) AS clicks, IFNULL(product_sale, 0) AS product_sale, IFNULL(GROUP_CONCAT(DISTINCT p.product_name), '') AS products
FROM ad
LEFT JOIN (
    SELECT ad_id, SUM(clicks) AS clicks
    FROM ad_insight
    WHERE date BETWEEN '2021-04-23' AND '2021-04-25'
    GROUP BY ad_id
) AS ai ON ai.ad_id = ad.ad_id
LEFT JOIN (
    SELECT ad_id, SUM(sale) AS product_sale
    FROM ads_products AS ap
    LEFT JOIN product_insight AS pi ON pi.product_id = ap.product_id
    WHERE date BETWEEN '2021-04-23' AND '2021-04-25'
    GROUP BY ad_id
) AS pi ON pi.ad_id = ad.ad_id
LEFT JOIN ads_products AS ap ON ap.ad_id = ad.ad_id
LEFT JOIN product AS p ON ap.product_id = p.product_id
GROUP BY ad.ad_id) AS x;

但是这个查询不起作用,因为它将prod1的销售额计算了两次。

共有1个答案

翟柏
2023-03-14

避免更改@@sql_mode的第一个SQL查询应该如下所示:

SELECT 
    ad.ad_name, 
    IFNULL(clicks, 0) AS clicks, 
    IFNULL(product_sale, 0) AS product_sale, 
    IFNULL(products, '') AS products
FROM ad
LEFT JOIN (
    SELECT ad_id, SUM(clicks) AS clicks
    FROM ad_insight
    WHERE date BETWEEN '2021-04-23' AND '2021-04-25'
    GROUP BY ad_id
) AS ai ON ai.ad_id = ad.ad_id
LEFT JOIN (
    SELECT ad_id, SUM(sale) AS product_sale
    FROM ads_products AS ap
    LEFT JOIN product_insight AS pi ON pi.product_id = ap.product_id
    WHERE date BETWEEN '2021-04-23' AND '2021-04-25'
    GROUP BY ad_id
) AS pi ON pi.ad_id = ad.ad_id
LEFT JOIN (
    SELECT ap.ad_id, GROUP_CONCAT(DISTINCT p.product_name) AS products
    FROM ads_products AS ap 
    JOIN product AS p ON ap.product_id = p.product_id
    GROUP BY ap.ad_id
) AS p ON ad.ad_id = p.ad_id 
WHERE clicks > 0;

计算汇总行的方法相同,但不在子查询中分组:

SELECT 
    total_ads, 
    p.all_products,
    ai.total_clicks, 
    pi.total_product_sale
FROM (
    SELECT SUM(clicks) AS total_clicks
    FROM ad_insight
    WHERE date BETWEEN '2021-04-23' AND '2021-04-25' AND clicks>0
) AS ai 
JOIN (
    SELECT SUM(sale) AS total_product_sale
    FROM product_insight AS pi 
    WHERE date BETWEEN '2021-04-23' AND '2021-04-25'
        AND EXISTS (
          SELECT 1 FROM ad_insight ai 
          JOIN ads_products AS ap ON ap.ad_id = ai.ad_id
          WHERE ap.product_id = pi.product_id AND ai.clicks > 0 AND
                date BETWEEN '2021-04-23' AND '2021-04-25'
        )
) AS pi 
JOIN (
    SELECT 
        COUNT(DISTINCT ad_id) AS total_ads, 
        GROUP_CONCAT(DISTINCT p.product_name) AS all_products
    FROM ads_products AS ap 
    JOIN product AS p ON ap.product_id = p.product_id
    WHERE EXISTS (
      SELECT 1 FROM ad_insight ai 
      WHERE ap.ad_id = ai.ad_id AND ai.clicks > 0 AND
            date BETWEEN '2021-04-23' AND '2021-04-25'
    )
) AS p

问题是您试图连接不同的数据集(clicks和sales),这导致聚合中出现冗余行。

小提琴

 类似资料:
  • 问题内容: 这是我的代码,我需要对列表中未定义数量的元素求和。这该怎么做? 我的输入: 输入后,我通过删除第一个元素。在列表之后,我需要对列表中的所有元素求和。 在这种情况下,总和为18。请注意,未定义元素数。 问题答案: 您可以使用内置的sum()来对列表中的数字求和: 它将汇总所有数量的项目。例: 对于您的具体情况 : 对于您的数据,首先将数字转换为数字,然后将数字求和: 这将适用于列表中 未

  • 本文向大家介绍OCaml 汇总列表中的数据,包括了OCaml 汇总列表中的数据的使用技巧和注意事项,需要的朋友参考一下 示例 的List.fold_left和List.fold_right功能是实现列表聚集的外逻辑高阶函数。汇总列表(有时也称为简化列表)意味着计算从对该列表中所有项目的顺序检查得出的值。 列表模块的文档指出: List.fold_left f a [b1; ...; bn]是f (

  • 问题内容: 抱歉,TITLE不够具体。我将尝试解释:我是SQL的新手。我在工作,并写了一个查询,该查询有9列,可从许多表中获取信息。第9列是机器类型的名称,第3列是代表一台机器工作一个月的时间的值。我需要添加第10列,该列将针对每种类型的计算机,该类型的最大值为3列。假设有5台XR类型的机器(表中的5行),其时间(第3列)为1,2,3,4,5(以小时为单位)。我需要在第10列中,计算机类型为XR的

  • 本文向大家介绍javascript获取网页宽高方法汇总,包括了javascript获取网页宽高方法汇总的使用技巧和注意事项,需要的朋友参考一下 document.body.clientWidth - 网页可见区域宽 document.body.clientHeight - 网页可见区域高 document.body.offsetWidth - 网页可见区域宽,包括边线和滚动条的宽 document

  • 本文向大家介绍javascript的BOM汇总,包括了javascript的BOM汇总的使用技巧和注意事项,需要的朋友参考一下 setTimeout()  //延时器,只执行一次代码 clearTimeout()  //清除演示器 setIntervla()  //定时器,根据指定时间间隔执行一次代码 clearInterval()  //清除定时器 window.history.length