英文:
How to get quarterly data from monthly in SQL Server
问题
我有一个表格，其中包含类似以下的月度数据。
这里我的 AvgSpeedOfAnswer 列是这样计算的：
avg(SpeedOfAnswer)
表格：
| 日期 | AvgSpeedOfAnswerMonth |
|---|---|
| 2022年7月 | 20.8 |
| 2022年8月 | 22.6 |
| 2022年9月 | 24.9 |
现在我的需求是，我需要创建一个查询，从上面的月度表格数据中获取季度数据。
我编写了一个查询如下：
SELECT
'季度' AS 趋势类型,
DATEADD(Q, DATEDIFF(Q, 0, TrendStartdate), 0) AS 趋势开始日期,
SUM(AvgSpeedOfAnswer)
FROM
Month_Stats
GROUP BY
DATEADD(Q, DATEDIFF(Q, 0, TrendStartdate), 0)
ORDER BY
DATEADD(Q, DATEDIFF(Q, 0, TrendStartdate), 0)
我不确定 AvgSpeedOfAnswer 应该取什么值。
是 SUM(AvgSpeedOfAnswerMonth) 还是 AVG(AvgSpeedOfAnswerMonth) 还是 AVG(SUM(AvgSpeedOfAnswerMonth))？
有人可以建议一下吗？
英文:
I have table where I am having monthly data like below.
Here my AvgSpeedOfAnswer column is calculated like this:
avg(SpeedOfAnswer)
Table:
| Date | AvgSpeedOfAnswerMonth |
|---|---|
| 7/1/2022 | 20.8 |
| 8/1/2022 | 22.6 |
| 9/1/2022 | 24.9 |
Now my requirement is I need to create a query where I can get quarterly data from above monthly table data.
I wrote a query like this:
SELECT
'Quarterly' AS TrendType,
DATEADD(Q, DATEDIFF(Q, 0, TrendStartdate), 0) AS TrendStartdate,
SUM(AvgSpeedOfAnswer)
FROM
Month_Stats
GROUP BY
DATEADD(Q, DATEDIFF(Q, 0, TrendStartdate), 0)
ORDER BY
DATEADD(Q, DATEDIFF(Q, 0, TrendStartdate), 0)
I am not sure what should I need to take for AvgSpeedOfAnswer.
Is it SUM(AvgSpeedOfAnswerMonth) or AVG(AvgSpeedOfAnswerMonth) or AVG(SUM(AvgSpeedOfAnswerMonth))?
Could anyone please suggest?
答案1
得分: 1
你可以使用 DATEPART 和 QUARTER
CREATE TABLE tabl11
([Date] datetime, [AvgSpeedOfAnswerMonth] DECIMAL(10,1))
;
INSERT INTO tabl11
([Date], [AvgSpeedOfAnswerMonth])
VALUES
('2022-07-01 02:00:00', 20.8),
('2022-08-01 02:00:00', 22.6),
('2022-09-01 02:00:00', 24.9)
;
``` status
3 行受影响
SELECT YEAR([Date]), DATEPART(QUARTER, [Date]), SUM([AvgSpeedOfAnswerMonth]) sum_quarterly FROM tabl11
GROUP BY YEAR([Date]),DATEPART(QUARTER, [Date])
| (No column name) | (No column name) | sum_quarterly |
|---|---|---|
| 2022 | 3 | 68.3 |
<details>
<summary>英文:</summary>
you can use DATEPART and QUARTER
CREATE TABLE tabl11
([Date] datetime, [AvgSpeedOfAnswerMonth] DECIMAL(10,1))
;
INSERT INTO tabl11
([Date], [AvgSpeedOfAnswerMonth])
VALUES
('2022-07-01 02:00:00', 20.8),
('2022-08-01 02:00:00', 22.6),
('2022-09-01 02:00:00', 24.9)
;
``` status
3 rows affected
SELECT YEAR([Date]), DATEPART(QUARTER, [Date]), SUM([AvgSpeedOfAnswerMonth]) sum_quarterly FROM tabl11
GROUP BY YEAR([Date]),DATEPART(QUARTER, [Date])
| (No column name) | (No column name) | sum_quarterly |
|---|---|---|
| 2022 | 3 | 68.3 |
答案2
得分: 1
我认为你需要使用平均值 (AvgSpeedOfAnswerMonth) 来获得季度平均速度。Sum(AvgSpeedOfAnswerMonth) 和 avg(sum(AvgSpeedOfAnswerMonth)) 给出相同的值，即季度值的总和。
英文:
I think you need avg (AvgSpeedOfAnswerMonth) to get the quarterly average speed over the quarter. Sum(AvgSpeedOfAnswerMonth) and avg(sum(AvgSpeedOfAnswerMonth) give the same value, which is the sum of the quarterly values.
答案3
得分: 0
使用带有参数QUARTER的DATEPART函数，如下所示：
select '季度' as 季度, date as 日期 from yourTable
group by DATEPART(quarter, date)
order by DATEPART(quarter, date)
英文:
Use the DATEPART function with parameter QUARTER, like
select 'Quarterly' as Quarterly, date as date from yourTable
group by DATEPART(quarter, date)
order by DATEPART(quarter, date)
答案4
得分: 0
如其他人所提到的，
您需要使用DATEPART
SELECT
'季度' AS TrendType,
DATEFROMPARTS(YEAR(Date), ((DATEPART(Q, Date) - 1) * 3) + 1, 1) AS TrendStartdate,
AVG(AvgSpeedOfAnswerMonth) AS AvgSpeedOfAnswerQuarter
FROM
Month_Stats
GROUP BY
YEAR(Date),
DATEPART(Q, Date)
ORDER BY
YEAR(Date),
DATEPART(Q, Date)
英文:
As others mentioned.
You need to use DATEPART
SELECT
'Quarterly' AS TrendType,
DATEFROMPARTS(YEAR(Date), ((DATEPART(Q, Date) - 1) * 3) + 1, 1) AS TrendStartdate,
AVG(AvgSpeedOfAnswerMonth) AS AvgSpeedOfAnswerQuarter
FROM
Month_Stats
GROUP BY
YEAR(Date),
DATEPART(Q, Date)
ORDER BY
YEAR(Date),
DATEPART(Q, Date)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论