英文:
Mysql - group by week but start counting from 1
问题
您想将查询结果按照从第一周开始划分，而不是按照年中的周数划分。您可以使用以下查询来实现这一目标：
SELECT
(TO_DAYS(sr.transactionDate) - TO_DAYS('2022-05-01')) DIV 7 + 1 AS custom_week_number,
SUM(sr.digitalEarnings)
FROM
sales_report AS sr
WHERE sr.transactionDate BETWEEN '2022-05-01' AND '2022-12-31'
GROUP BY custom_week_number
ORDER BY custom_week_number;
此查询将根据从'2022-05-01'开始的自定义周数对数据进行分组，以便从第一周开始划分。
英文:
Lets say I have this query:
select
week(sr.transactionDate) AS week_number,
sum(sr.digitalEarnings)
from
sales_report as sr
where `sr`.`transactionDate` BETWEEN '2022-05-01' AND '2022-12-31'
group by week_number
order by week_number;
Which returns data like so:
I know this is the "week in the year" number but I'm actually trying to get the data divided into weeks starting from 1. i.e Week 1 in this period, week 2 in this period etc.
How would I achieve this?
答案1
得分: 1
你可以直接从交易日期计算周数。假设你没有改变默认的_week_format系统变量，那么你是从星期日开始计算的；调整你的范围开始日期，找到在交易日期之前或之前的星期日，并从那天开始计算周数，从1开始而不是从0开始：
floor(datediff(sr.transactionDate,'2022-05-01' - interval dayofweek('2022-05-01')-1 day) / 7) + 1
我强烈建议不要使用week()函数；它在跨年或靠近年初或年末时的行为通常不适合报告目的。尤其是对比或减去两个周值可能会引发问题。
英文:
You can calculate week number directly from the transaction date. Assuming you have left the default_week_format system variable unchanged, you are doing weeks starting with Sunday; adjust your range start date to find the Sunday on or before, and count the weeks from that to the transaction date, adding one to start with week 1, not week 0:
floor(datediff(sr.transactionDate,'2022-05-01' - interval dayofweek('2022-05-01')-1 day) / 7) + 1
I strongly suggest not using week() at all; its behavior across years or near the beginning or end of years is usually not friendly for reporting purposes. Especially subtracting or comparing two week values can be problematic.
答案2
得分: 0
如何使用 `ROW_NUMBER()`，尽管这只在版本8及以上可用：
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number
SELECT
week(sr.transactionDate) AS 周数,
ROW_NUMBER() OVER (PARTITION BY 周数 ORDER BY 周数) AS 周次,
sum(sr.digitalEarnings)
FROM
sales_report as sr
WHERE `sr`.`transactionDate` BETWEEN '2022-05-01' AND '2022-12-31'
GROUP BY 周数
ORDER BY 周数;
英文:
how about using ROW_NUMBER() although this is only available in v8 and above:
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number
SELECT
week(sr.transactionDate) AS week_number,
ROW_NUMBER() OVER (GROUP BY week_number ORDER BY week_number) AS week_n,
sum(sr.digitalEarnings)
FROM
sales_report as sr
WHERE `sr`.`transactionDate` BETWEEN '2022-05-01' AND '2022-12-31'
GROUP BY week_number
ORDER BY week_number;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论