如何从包含0值的SQL中获取最近7天的总和

Tikky 发表于 Dev

提基

在我的CRM系统中，我有带潜在客户的表格。我想做一个图表，看看过去7天增加了多少线索。为了这个目的，我需要从上周开始每天分开一笔款项。

我叫的表tab_leads带有lead_id（整数）和lead_create_date（时间戳，格式：0000-00-00 00:00:00）

所以我需要这样的东西：

第1-10天
第2天-第0天
第3-5天
第4-0天
第5-9天
第6-15天
第七天（今天）-0

目前，我使用此查询：

SELECT
    DATE(lead_create_date) AS `Date`,
    COUNT(*) AS `Leads`
FROM
    tab_leads
WHERE
    lead_create_date >=  CURRENT_DATE - INTERVAL 6 DAY
GROUP BY
    DATE(lead_create_date)

但是问题是，如果在那些日子中的任何一天我们都没有任何数据（例如周末），我得到的总和将少于7。前任：

第1-10天
第2-5天
第3-9天
第4-15天

为了绘制图表，我需要始终有七个总和，即使值为0。如何在MySQL或MySQL + PHP中做到这一点？

..UPDATE：我只是试图创建SQL Fiddle并取得成功。样本数据：

CREATE TABLE tab_leads (
  `lead_id` int,
  `lead_create_date` timestamp
) ENGINE=InnoDB 

INSERT INTO tab_leads
  (`lead_id`, `lead_create_date`) 
VALUES
(0, '2015-05-02 05:30:40'),
(1, '2015-05-02 00:00:00'),
(2, '2015-05-03 00:00:00'),
(3, '2015-05-03 00:00:00'),
(4, '2015-05-05 00:00:00'),
(5, '2015-05-06 00:00:00'),
(6, '2015-05-07 00:00:00'),
(7, '2015-05-08 00:00:00'),
(8, '2015-05-08 00:00:00')
;

阿比克·查克拉博蒂（Abhik Chakraborty）

在这里，您要查询的是

select 
t1.Date,
coalesce(t2.Leads, 0) AS Leads
from
(
  select DATE_FORMAT(a.Date,'%Y-%m-%d') as Date
  from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
  ) a
  where a.Date BETWEEN curdate() - interval 7 DAY AND curdate()
)t1
left join
(
  SELECT
  DATE(lead_create_date) AS `Date`,
  COUNT(*) AS `Leads`
  from  tab_leads
  WHERE
  lead_create_date >=  CURRENT_DATE - INTERVAL 6 DAY
  GROUP BY DATE(lead_create_date)
)t2
on t2.Date = t1.Date
group by t1.Date
order by t1.Date desc

查询的第一部分只是生成给定范围的日期，然后将其用作左表并向原始查询进行左联接。

从样本数据中，您将得到以下结果：

+------------+-------+
| Date       | Leads |
+------------+-------+
| 2015-05-08 |     2 |
| 2015-05-07 |     1 |
| 2015-05-06 |     1 |
| 2015-05-05 |     1 |
| 2015-05-04 |     0 |
| 2015-05-03 |     2 |
| 2015-05-02 |     2 |
| 2015-05-01 |     0 |
+------------+-------+
8 rows in set (0.02 sec)

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。