获取过去30天中每天的查询结果

jjhzyzn0 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(200)

下面是我在过去24小时内提取一些数据的查询。

SELECT
    s.symbol,
    count(cs.symbol_id) AS mentions
FROM symbols s
LEFT JOIN comments_symbols cs ON cs.symbol_id = s.id
LEFT JOIN comments c ON c.id = cs.comment_id
WHERE c.`date` > DATE_SUB(NOW(), INTERVAL 1 DAY)
GROUP BY (s.symbol)
ORDER BY mentions
DESC LIMIT 15

但是，我需要24小时间隔的数据为过去30天，以显示一个30天的图表。
在过去的30天里，我不是每天执行30次这个查询，而是有没有一种方法可以只执行一次查询？
似乎每个页面加载执行30次这个查询可能不是最好的方法，不是吗？
我希望我解释清楚，请让我知道如果任何细节是模糊的。

sql mysql

来源：https://stackoverflow.com/questions/62774086/getting-the-results-of-a-query-for-each-day-in-the-past-30-days

1条答案

按热度按时间

kt06eoxx1#

我假设你有一份日期清单。如果不想列出它们，可以生成它们：

with recursive dates as (
      select curdate() - interval 30 day as dte
      union all
      select dte + interval 1 day
      from dates
      where dte < curdate()
     )

第二，市场 LEFT JOIN 似乎是多余的，因为您正在使用 LIMIT . 不过，我还是把它放进去。使用 cross join 要为每一天和符号生成一行。然后聚合：

SELECT s.symbol, COUNT(cs.symbol_id) AS mentions
FROM dates d CROSS JOIN
     symbols s LEFT JOIN
     comments_symbols cs
     ON cs.symbol_id = s.id LEFT JOIN
     comments c
     ON c.id = cs.comment_id AND
        c.date >= d.dte AND
        c.date < d.date + interval 1 day
GROUP BY d.dte, s.symbol
ORDER BY d.dte, mentions DESC

最后，为了每天得到15个，我们将其放入cte并使用窗口函数：

WITH sm as (
      SELECT d.dte, s.symbol, COUNT(cs.symbol_id) AS mentions
      FROM dates d CROSS JOIN
           symbols s LEFT JOIN
           comments_symbols cs
           ON cs.symbol_id = s.id LEFT JOIN
           comments c
           ON c.id = cs.comment_id AND
              c.date >= d.dte AND
              c.date < d.date + interval 1 day
      GROUP BY d.dte, s.symbol
     )
SELECT cs.*
FROM (SELECT cs.*,
             ROW_NUMBER() OVER (PARTITION BY dte ORDER BY mentions DESC) as seqnum
      FROM cs
     ) cs
WHERE seqnum <= 15;
ORDER BY dte, mentions DESC;

赞(0）回复(0）举报 2021-07-24

我来回答

获取过去30天中每天的查询结果

1条答案

相关问题

热门标签

最新问答