过去 5 天按天分组

发布于 2024-11-26 09:31:40 字数 404 浏览 4 评论 0原文

我正在尝试选择过去 5 天的整数字段的总和，并且需要每天对其进行分组。

我在确定分组时遇到了一些问题。到目前为止，这是我的 sql 查询：

select 
    sum(`amount_sale`) as total 
from `sales` 
where the_date >= unix_timestamp((CURDATE() - INTERVAL 5 DAY))

它可以很好地生成所有 5 天的总和，但我需要对其进行分解，以便它显示过去 5 天每一天的总和，即：

第 1 天 - 200 美元
第 2 天 - 500 美元
第 3 天 - 20 美元

等

原文

I am trying to select the sum of an integer field for the past 5 days, and I need to group it for each day.

I'm having a bit of issues figuring out the grouping. Here's my sql query so far:

select 
    sum(`amount_sale`) as total 
from `sales` 
where the_date >= unix_timestamp((CURDATE() - INTERVAL 5 DAY))

that works fine for generating the sum for all 5 days together, but I need to break this down so that it shows the sum for each of the past 5 days i.e:

day 1 - $200
day 2- $500
day 3 - $20

etc.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

可遇━不可求 2024-12-03 09:31:40

SELECT  DATE(FROM_UNIXTIME(the_date)) AS dt, SUM(amount_sale) AS total
FROM    sales
WHERE   the_date >= UNIX_TIMESTAMP((CURDATE() - INTERVAL 5 DAY))
GROUP BY
        dt

对于缺少的日期返回 0：

SELECT  dt, COALESCE(SUM(amount_sale), 0) AS total
FROM    (
        SELECT  CURDATE() - INTERVAL 1 DAY AS dt
        UNION ALL
        SELECT  CURDATE() - INTERVAL 2 DAY AS dt
        UNION ALL
        SELECT  CURDATE() - INTERVAL 3 DAY AS dt
        UNION ALL
        SELECT  CURDATE() - INTERVAL 4 DAY AS dt
        UNION ALL
        SELECT  CURDATE() - INTERVAL 5 DAY AS dt
        ) d
LEFT JOIN
        sales
ON      the_date >= UNIX_TIMESTAMP(dt)
        AND the_date < UNIX_TIMESTAMP(dt + INTERVAL 1 DAY)
GROUP BY
        dt

这不是一个非常优雅的解决方案，但是，MySQL 缺乏从头开始生成记录集的方法。

SELECT  DATE(FROM_UNIXTIME(the_date)) AS dt, SUM(amount_sale) AS total
FROM    sales
WHERE   the_date >= UNIX_TIMESTAMP((CURDATE() - INTERVAL 5 DAY))
GROUP BY
        dt

To returns 0 for missing dates:

SELECT  dt, COALESCE(SUM(amount_sale), 0) AS total
FROM    (
        SELECT  CURDATE() - INTERVAL 1 DAY AS dt
        UNION ALL
        SELECT  CURDATE() - INTERVAL 2 DAY AS dt
        UNION ALL
        SELECT  CURDATE() - INTERVAL 3 DAY AS dt
        UNION ALL
        SELECT  CURDATE() - INTERVAL 4 DAY AS dt
        UNION ALL
        SELECT  CURDATE() - INTERVAL 5 DAY AS dt
        ) d
LEFT JOIN
        sales
ON      the_date >= UNIX_TIMESTAMP(dt)
        AND the_date < UNIX_TIMESTAMP(dt + INTERVAL 1 DAY)
GROUP BY
        dt

This is not a very elegant solution, however, MySQL lacks a way to generate recordsets from scratch.

回复收藏 0 原文