我有3个表,分别是用户、站点和site_traffic。users表包含用户名和用户的其他详细信息。每个用户有1个或多个站点,这些站点存储在站点表中。现在每个网站都有自己的流量数据。
我想要完成的是为所有用户选择每个站点没有流量数据的所有日期。这将显示所有用户的名称,每个用户的site_id以及这些站点没有数据的日期。
作为这个查询,我能够得到没有数据的日期只有一个特定的用户。如何修改此查询以列出所有用户及其站点以及每个站点没有数据的日期?
这是我的查询:
SELECT b.dates_without_data
FROM (
SELECT a.dates AS dates_without_data
FROM (
SELECT CURDATE() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as dates
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.dates >= DATE_SUB(DATE_SUB(NOW(),INTERVAL 1 DAY), INTERVAL 35 DAY)
) b
WHERE b.dates_without_data NOT IN (
SELECT recorded_on
FROM site_traffic, sites, users
WHERE site_traffic.site_id = sites.site_id
AND sites.user_id = users.user_id
AND users.user_id = 1
)
AND b.dates_without_data < DATE_SUB(NOW(),INTERVAL 1 DAY)
ORDER BY b.dates_without_data ASC
谢谢大家的帮助。
我会使用反连接模式。
首先,在生成的可能日期列表和所有站点之间执行交叉连接操作。这样就得到了每个站点每天的行数。然后继续执行连接到users表的操作。
诀窍是反连接。我们将所有网站和所有日期的集合,然后"匹配"到site_traffic的行。我们只想返回不匹配的行。我们可以使用外部连接来实现这一点,然后在WHERE子句中添加一个条件,在找到匹配时排除该行。只留下不匹配的行。
像这样:
SELECT s.site_id
, u.user_id
, d.dt AS date_without_data
FROM (
SELECT DATE(NOW()) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS dt
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
HAVING dt >= DATE(NOW()) + INTERVAL -1-35 DAY
AND dt < DATE(NOW()) + INTERVAL -1 DAY
) d
CROSS
JOIN site s
JOIN users u
ON u.user_id = s.user_id
LEFT
JOIN site_traffic t
ON t.site_id = s.site_id
ON t.recorded_on >= d.dt
AND t.recorded_on < d.dt + INTERVAL 1 DAY
WHERE t.site_id IS NULL
ORDER BY s.site_id, u.user_id
这里的技巧是WHERE子句中的条件。在site_traffic
中找到匹配行的任何行对于site_id
都将具有非null值。(在连接条件中与site_id
的相等性比较保证了我们。)因此,如果我们排除所有具有非null值的行,则剩下没有匹配的行。
(我假设recorded_on是一个日期时间,所以我使用了范围比较…来匹配给定日期内recorded_on
的任何值。如果recorded_on
实际上是date
(没有时间),那么我们可以做一个更简单的相等比较。
将u
和s
表中的表达式添加到SELECT列表中。
有些人认为内联视图d
(生成"所有日期"列表)看起来有点乱。但我觉得挺好的。
如果MySQL提供一个表值函数,或者其他一些"更漂亮"的机制来生成一系列整数值,那就太好了。
我会在视图查询本身中包含所有关于日期的条件,在视图中完成它,而不必与外部查询混淆。