首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >如何从包含0值的SQL中获得最后七天的单独和

问如何从包含0值的SQL中获得最后七天的单独和
EN

Stack Overflow用户
提问于 2015-05-08 12:41:32
回答 3查看 1.2K关注 0票数 1

在我的CRM系统中,我有带引线的桌子。我想做一个图表,看看在过去的7天里增加了多少线索。为了达到这个目的,我需要从上周开始的每一天都有之和,。

我的表tab_leads附带了lead_id (整数)和lead_create_date (时间戳,格式: 0000-00- 00 :00:00)。

所以我需要这样的东西:

  • 第一天-第十天
  • 第2天-0
  • 第3天-第5天
  • 第4天-0
  • 第5天-第9天
  • 第6天-第15天
  • 第七天(今日)-0

现在,我提出了这个问题:

代码语言:javascript
复制
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笔。例:

  • 第一天-第十天
  • 第二天-第五天
  • 第3天-第9天
  • 第4天-第15天

在绘制图表时,我需要始终使用7和,即使值为0。如何在MySQL或MySQL + PHP中做到这一点?

..UPDATE:我只是想在成功的情况下创建SQL。样本数据:

代码语言:javascript
复制
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')
;
EN

回答 3

Stack Overflow用户

回答已采纳

发布于 2015-05-08 14:07:50

在这里,查询是

代码语言:javascript
复制
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

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

从示例数据中,您将获得如下结果

代码语言:javascript
复制
+------------+-------+
| 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)
票数 2
EN

Stack Overflow用户

发布于 2015-05-08 13:12:59

不漂亮,但试试这个?

代码语言:javascript
复制
            SELECT tmp.dt, src.Leads
            FROM (
                SELECT CURRENT_DATE - INTERVAL 6 DAY as dt UNION
                    SELECT CURRENT_DATE - INTERVAL 5 DAY UNION
                    SELECT CURRENT_DATE - INTERVAL 4 DAY UNION
                    SELECT CURRENT_DATE - INTERVAL 3 DAY UNION
                    SELECT CURRENT_DATE - INTERVAL 2 DAY UNION
                    SELECT CURRENT_DATE - INTERVAL 1 DAY UNION
                    SELECT CURRENT_DATE) as tmp
                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 )
                ) as src on tmp.dt = src.`Date`
票数 2
EN

Stack Overflow用户

发布于 2015-05-08 13:50:02

因为在原始表中没有给定日期的数据,所以不能只返回0的行。您可能会看到一些常见的例子,如:

为每个用户提供每个字段的计数,即使它是0。

这些例子是可以完成的,假设每个user都有一个记录,即使没有some field的记录。在您的例子中,您没有第2天、第4天或第7天的记录,因此它们不能返回。

要解决这个问题,您需要创建一个最近7天的临时表。使用一个给出的这里示例

代码语言:javascript
复制
select a.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 DATE_SUB(NOW(), INTERVAL 7 DAY) and NOW();

这会给你提供你需要的临时桌子。然后,您可以像这样预置您的聚合:

代码语言:javascript
复制
SELECT lead_create_date AS date, COUNT(*) AS numLeads
FROM myTable
GROUP BY lead_create_date;

然后将其外部连接到临时表,该表将填充0值:

代码语言:javascript
复制
SELECT tmp.date, COALESCE(m.numLeads, 0) AS numLeads
FROM(
  SELECT a.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 DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW()) tmp
LEFT JOIN(
  SELECT lead_create_date AS date, COUNT(*) AS numLeads
  FROM myTable 
  GROUP BY lead_create_date) m ON m.date = tmp.date
ORDER BY tmp.date;

看上去很可怕,但很管用。根据另一个答案中的评论,它看起来也很好。

下面是一个SQL Fiddle示例。

编辑

澄清一下,我给出的第一个查询只是为了说明如何创建一个包含最后7个日期的临时表。第二个查询显示如何在整个表上预先形成聚合,以获取表中所有日期的计数。第三个查询将它们组合在一起,只显示最后七天的计数,这就是您正在寻找的答案。

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/30124147

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档