mysql - MySQL中如何确定特定时间间隔的查询？

Question

我正在运行一些crontabs哪个触发 R-Scripts我在哪里加载Google Analytics Data对于特定的时间间隔。通常是间隔:

Today - 1 to Today - 14 days对应如下语句:

subset(mydata, date >= Sys.Date()-14 & date <= Sys.Date()-1)

我想添加一些MySQL-Query到那个R-Script为了获得一些数据，它使用相同的时间间隔。我的表格具有以下形式:

`pictures`                     `music`                `likes`

 id  date_of_upload         id   pictures_id          id   pictures_id

 1    2012-01-16              50       1283           287    12
 2    2012-02-17              25       736            2366   39
 ...  ...                     ...      ...            ...    ...
 6000 2016-01-23             

我的查询具有以下形式，我希望满足上限时间间隔:

SELECT 
      COUNT(p.id) AS pictures,
      COUNT(m.id) AS songs,
      COUNT(l.id) AS likes,
      CAST(p.date_of_upload AS DATE) AS Posted


FROM pictures p
      LEFT JOIN
       music m ON p.id = m.pictures_id
      LEFT JOIN
       likes l ON p.id = l.pictures_id
WHERE p.date_of_upload > DATE_ADD(CURRENT_DATE(), INTERVAL - 14 DAY)

但这似乎不是时间间隔的正确实现。

所需的输出可能如下所示:

 posted      songs   likes  picture

2016-01-23    20      30    3
2016-01-22    10      8     1
2016-01-21 
...
2016-01-07

Answer 1

我认为最简单的解决方案是使用COUNT(DISTINCT):

SELECT COUNT(DISTINCT p.id) AS pictures,
       COUNT(DISTINCT m.id) AS songs,
       COUNT(DISTINCT l.id) AS likes,
       CAST(p.date_of_upload AS DATE) AS Posted
FROM pictures p LEFT JOIN
     music m
     ON p.id = m.pictures_id LEFT JOIN
     likes l
     ON p.id = l.pictures_id
WHERE p.date_of_upload > DATE_ADD(CURRENT_DATE(), INTERVAL - 14 DAY)

问题可能是您在两个表之间获取笛卡尔积 - 图片、音乐和点赞的每个组合都有一个单独的行。

COUNT(DISTINCT) 是最简单的方法，但如果值很大，则效率很低。