最佳回答
0
华莉星宸 Lv7 资深互助 发布于2024-11-12 15:45
辣子鸡(提问者)
- 有啥简单点的么，就一个字段，最好可以用sql
华莉星宸
- 直接写的话，可以把表自己和自己关联两次，关联条件吧月份往前推
最佳回答
0
Z4u3z1 Lv6 专家互助 发布于2024-11-12 15:45
最佳回答
0
Chazzen Lv2 初级互助 发布于2024-11-12 15:47
WITH MonthlySums AS (
SELECT
FORMAT(date, 'yyyy-MM') AS year_month,
SUM(quantity) AS monthly_quantity
FROM
sales
GROUP BY
FORMAT(date, 'yyyy-MM')
),
ShiftedSums AS (
SELECT
s1.year_month,
s1.monthly_quantity +
COALESCE(s2.monthly_quantity, 0) +
COALESCE(s3.monthly_quantity, 0) AS cumulative_quantity
FROM
MonthlySums s1
LEFT JOIN
MonthlySums s2 ON s1.year_month = FORMAT(DATEADD(MONTH, -1, CAST(s2.year_month + '-01' AS DATE)), 'yyyy-MM')
LEFT JOIN
MonthlySums s3 ON s1.year_month = FORMAT(DATEADD(MONTH, -2, CAST(s3.year_month + '-01' AS DATE)), 'yyyy-MM')
)
SELECT
year_month,
cumulative_quantity
FROM
ShiftedSums
ORDER BY
year_month;
辣子鸡(提问者)
- 不只三个月的数据。。。要求是近一年的数据
Chazzen
- 参考这个看看
WITH MonthlySums AS (
SELECT
FORMAT(date, 'yyyy-MM') AS year_month,
SUM(quantity) AS monthly_quantity
FROM
sales
GROUP BY
FORMAT(date, 'yyyy-MM')
),
CumulativeSums AS (
SELECT
year_month,
monthly_quantity,
SUM(monthly_quantity) OVER (
PARTITION BY year_month
ORDER BY year_month
ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
) AS cumulative_quantity
FROM
MonthlySums
)
SELECT
year_month,
cumulative_quantity
FROM
CumulativeSums
ORDER BY
year_month
最佳回答
0
yzmnJCsJ7252186 Lv4 见习互助 发布于2024-11-12 15:59（编辑于 2024-11-12 16:00）
SELECT
DATE_FORMAT(日期, '%Y-%m') AS month, SUM(数量) AS total_sales
FROM
sales
WHERE
日期 BETWEEN DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 2 MONTH)
AND CURDATE()GROUP BY
DATE_FORMAT(日期, '%Y-%m')
ORDER BY
month DESC;
最佳回答
0
Zzz1002 Lv2 见习互助 发布于2024-11-14 08:31（编辑于 2024-11-14 09:30）
with tmp as(select to_date('2023-12-01','yyyy-MM-DD') as ym,100 as qty from dual
union
select to_date('2024-02-01','yyyy-MM-DD') as ym,500 as qty from dual
union
select to_date('2024-05-01','yyyy-MM-DD') as ym,300 as qty from dual
union
select to_date('2024-08-01','yyyy-MM-DD') as ym,200 as qty from dual
union
select to_date('2024-11-01','yyyy-MM-DD') as ym,400 as qty from dual
union
select to_date('2025-01-01','yyyy-MM-DD') as ym,100 as qty from dual)
select tmp.ym,sum(tmp1.QTY) as qty
from tmp
left join tmp tmp1
on tmp1.ym between ADD_MONTHS(tmp.ym,-3) and tmp.ym
group by tmp.ym
order by ym
最佳回答
0
JL98 Lv6 中级互助 发布于2024-11-14 09:17
postgresSQL为例：
WITH DateRange AS (
SELECT
DATE ,
SUM (QUANTITY) OVER (
ORDER BY DATE
RANGE BETWEEN INTERVAL '89' DAY PRECEDING AND CURRENT DAY
) AS total_quantity_last_90_days
FROM
sales
WHERE
DATE >= DATE_TRUNC ('month' , CURRENT_DATE ) - INTERVAL '2' MONTH -- 确保我们考虑的时间范围包含足够的历史数据
AND DATE < DATE_TRUNC ('month' , CURRENT_DATE ) + INTERVAL '1' MONTH -- 这个上界可以根据需要调整，确保不会超出查询的当前月份范围
)
SELECT
TO_CHAR (DATE_TRUNC ('month' , DATE ), 'YYYY/MM' ) AS month ,
total_quantity_last_90_days
FROM
DateRange
WHERE
EXTRACT (DAY FROM DATE ) = 1 -- 只选择每个月的第一天来展示结果，如果需要其他天的数据可以调整这里
ORDER BY
month ;
最佳回答
0
梦似幻亦真 Lv3 见习互助 发布于2024-11-15 17:19
with tt1 AS(
SELECT 年份，月份，SUM(数据) AS 合计
FROM 表
GROUP BY 年份，月份
)，
tt2 AS（
SELECT 年份,月份,合计 FROM tt1
UNION ALL
SELECT 年份,月份 +1,合计 FROM tt1
UNION ALL
SELECT 年份,月份 +2,合计 FROM tt1
）
SELECT 年份,月份,SUM(合计) AS 90天合计
FROM TT2
WHERE EXISTS(SELECT 1 FROM tt1 WHERE tt1.年份 = tt2.年份 AND tt1.月份 =tt2.月份 )
GROUP BY 年份,月份
这样子每个月就是三个月的合计值了
1min目标场景问卷
立即参与
