从 TIMESTAMP 字段 MYSQL 创建按日期和小时细分 [英] Create a by Date and hour breakdown from TIMESTAMP field MYSQL

问题描述

我正在尝试创建一个查询，该查询按日期和小时提供 ids 的细分，其中显示空白结果为零.

I'm trying to create a query that provides a breakdown of ids by date and hour which shows zeroes for blank results.

这是到目前为止的 MYSQL 查询:

Here's MYSQL query so far:

SELECT DATE(received),
  CONCAT(hourly.hour, ':00 - ', hourly.hour+1, ':00') AS Hours,  
  COALESCE(COUNT(id),0) AS "Leads"
FROM hourly
LEFT JOIN digital_lead ON hourly.hour=hour(digital_lead.received)
WHERE 
  digital_lead.received>=DATE_SUB('2014-11-01', INTERVAL 24 HOUR) AND
  digital_lead.received<=DATE_SUB('2014-11-26', INTERVAL 24 HOUR) 
GROUP BY DATE(received), hourly.hour
ORDER BY DATE(received)

我有一个从 0 到 23 的整数表，它们构成了 hourly 表.

I have a table of integers from 0 - 23 that make up the hourly table.

目前，这个查询只显示结果存在的时间段 - 像这样:

At the moment, this query only shows the timeslots where results exist - like so :

DATE(received)       Hours  Leads
2014-11-12           11:00 - 12:00  23
2014-11-12           12:00 - 13:00  19
2014-11-12           13:00 - 14:00  18
2014-11-12           14:00 - 15:00  17
2014-11-12           15:00 - 16:00  23
2014-11-12           16:00 - 17:00  13
2014-11-12           17:00 - 18:00  17

查询应该为 00:00 - 11:00 的时间段显示零结果，而不是从查询结果中遗漏它们.它似乎忽略了 COALESCE 函数，我尝试用 IFNULL(COUNT(id), 0) 替换它，没有任何区别.

The query should show zero results for the timeslots from 00:00 - 11:00 rather than missing them out from the query result. It seems to be ignoring the COALESCE function, which I've tried substituting with IFNULL(COUNT(id), 0) with no difference.

我已经尝试过这个问题/答案中的解决方案 - STACKOVERFLOW链接

I've tried the solution in this question/answer - STACKOVERFLOW LINK

但是，这对我不起作用.

However, this doesn't work for me.

有什么建议吗?

编辑

这里有一个 SQL FIDDLE 了解更多详情

Here's an SQL FIDDLE for further details

推荐答案

好的，这花了一些时间.这是我最能想到的.可能有更好的方法来做到这一点，但加入时丢失的日期或时间总是很复杂.我给出了几个答案 与缺少日期有关，但这是完全不同的.

Ok this took some time. This is what I could best come up with. May be there is a better way to do it but the missing date or time while joining is always complicated. I have given few answers related to missing dates but this is completely different.

因此，我使用相同的想法在查询中进行了一些更改以检索如下数据

So using the same idea I did some changes within the query to retrieve the data as below

select
t1.date_received,
t1.Hours,
COALESCE(t2.`Leads`,0) as `Leads`
from
(
  select
  distinct DATE(received) as date_received,
  concat (DATE(received),'-',h.hour) as date_hour,
  CONCAT(h.hour, ':00 - ', h.hour+1, ':00') AS Hours 
  from digital_lead
  cross join (select hour from hourly)h
)t1
left join
(
  SELECT DATE(received) as date_received,
  concat (DATE(received),'-',hour) as date_hour,
  CONCAT(hourly.hour, ':00 - ', hourly.hour+1, ':00') AS Hours,  
  COALESCE(COUNT(id),0) AS "Leads"
  FROM hourly
  LEFT JOIN digital_lead ON hourly.hour=hour(digital_lead.received)
  and digital_lead.received>=DATE_SUB('2014-11-01', INTERVAL 24 HOUR)
  and digital_lead.received<=DATE_SUB('2014-11-26', INTERVAL 24 HOUR) 
  GROUP BY DATE(received), hourly.hour
)t2
on t1.date_hour = t2.date_hour
order by t1.date_received,
cast(substring_index(t1.Hours,':',1) as unsigned)

FIDDLE

这篇关于从 TIMESTAMP 字段 MYSQL 创建按日期和小时细分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！