聚合查询优化

Question

我需要按天汇总所有的销售额来建立图表。

我有这么一张桌子

CREATE TABLE `transaction` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` varchar(255) NOT NULL,
  `purchase_date` int(10) unsigned NOT NULL DEFAULT '0',
  `expires_date` int(10) unsigned NOT NULL DEFAULT '0',
  `expires_date_calculated` int(10) unsigned NOT NULL DEFAULT '0',
  `payment_account_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `payment_account_id` (`payment_account_id`),
  KEY `expires_date` (`expires_date`)
);

使用以下数据

INSERT INTO transaction
 (product_id, purchase_date, expires_date, expires_date_calculated, payment_account_id) 
VALUES 
 ('a', 1164357246, 0, 1264357246, 2),
 ('a', 1345117819, 0, 1435413246, 1),
 ('b', 1345117907, 0, 1440683646, 2),
 ('c', 1345119400, 1464357246, 0, 5),
 ('b', 1345119625, 0, 1464357246, 10),
 ('a', 1345120085, 0, 1464357246, 2),
 ('b', 1345121092, 0, 1464357246, 5),
 ('a', 1464357246, 0, 1564357246, 2) ;

我有一个函数，它用不同的日期值32次触发以下查询

SELECT COUNT(DISTINCT payment_account_id) AS cnt
FROM transaction
WHERE (purchase_date < 1345120085  
       AND (expires_date > 1345120085 OR expires_date_calculated > 1345120085))
  AND (
    `product_id` IN
    (
      'a',
      'b'
    )
  );

它的工作非常慢。我想用32 SUM(如果())来代替它。

SELECT
  sum(IF((purchase_date < 1345120085 
          AND (expires_date > 1345120085 OR expires_date_calculated > 1345120085)), 
        1, 0)) AS day1
#...
FROM (
       SELECT
         payment_account_id,
         (purchase_date)           AS purchase_date,
         (expires_date)            AS expires_date,
         (expires_date_calculated) AS expires_date_calculated
       FROM transaction
       WHERE
         `product_id` IN
         (
           'a',
           'b'
         )
         AND payment_account_id IS NOT NULL
        GROUP BY payment_account_id
     ) AS tmp;

SQL与示例

但我得到了错误的结果。我做错什么了？

Answer 1

好的。我找到了解决办法。

SELECT 
  SUM(IF(day1 >0, 1, 0))
  #...
  FROM(
      SELECT
      payment_account_id, sum(day1) AS day1
      #...
        FROM (
           SELECT
             payment_account_id,
             IF((purchase_date < 1345120085 AND (expires_date > 1345120085 OR expires_date_calculated > 1345120085)), 1, 0) AS day1
            #...
           FROM transaction
           WHERE
             `product_id` IN
             (
               'a',
               'b'
             )
             AND payment_account_id IS NOT NULL
          ) AS tmp
      GROUP BY payment_account_id
     )
     AS tmp1;

Answer 2

可以将原始查询转换为多个条件，如下所示：

SELECT COUNT(DISTINCT CASE WHEN purchase_date < 1345120085  
                            AND ( expires_date > 1345120085 
                               OR expires_date_calculated > 1345120085) 
                        THEN payment_account_id
                      END
            ) AS cnt1,
       COUNT(DISTINCT CASE WHEN <condition 2>
                        THEN payment_account_id
                      END
            ) AS cnt2,
       -- etc.
FROM `transaction`
WHERE product_id IN ('a', 'b') ;

你的解决方案似乎也是正确的。可以简化为将两个嵌套派生表删除为一个表：

SELECT 
    SUM(day1 > 0) AS cnt1,
    SUM(day2 > 0) AS cnt2,
    -- etc,
FROM
  ( SELECT
        payment_account_id, 
        SUM( purchase_date < 1345120085 
             AND ( expires_date > 1345120085 
                OR expires_date_calculated > 1345120085)
           ) AS day1,
        SUM( <condition 2> ) AS day2,
        --- etc, 
    FROM `transaction`
    WHERE product_id IN ('a', 'b')
      AND payment_account_id IS NOT NULL
    GROUP BY payment_account_id
  ) AS tmp ;