在mysql中以Sum开头

这是数据库的模式

CREATE TABLE `visita_prodotto`  (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`prezzo` int(15) UNSIGNED NULL DEFAULT NULL COMMENT 'price',
`id_visita` int(10) UNSIGNED NOT NULL COMMENT 'visit id');

CREATE TABLE `visita`  (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`id_campagna` int(10) UNSIGNED NOT NULL COMMENT 'project',
`dataorainizio` datetime(0) NOT NULL COMMENT 'start date');
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (53, 8, 5000);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (54, 8, 8000);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (55, 9, 4000);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (56, 9, 3000);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (66, 11, 5544);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (71, 12, 7500);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (77, 13, 5433);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (85, 17, 7200);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (89, 15, 4500);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (94, 16, 3200);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (99, 16, 5800);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (104, 16, 99999);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (107, 22, 7500);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (111, 22, 129999);
INSERT INTO `visita_prodotto`(`id`, `id_visita`, `prezzo`) VALUES (113, 22, 99999);
INSERT INTO  `visita`(`id`, `dataorainizio`, `id_campagna`) VALUES (8, '2020-02-12 15:23:00', 5);
INSERT INTO  `visita`(`id`, `dataorainizio`, `id_campagna`) VALUES (9, '2020-02-14 01:59:45', 5);
INSERT INTO  `visita`(`id`, `dataorainizio`, `id_campagna`) VALUES (11, '2020-02-19 14:27:17', 5);
INSERT INTO  `visita`(`id`, `dataorainizio`, `id_campagna`) VALUES (12, '2020-02-19 15:43:42', 5);
INSERT INTO  `visita`(`id`, `dataorainizio`, `id_campagna`) VALUES (13, '2020-02-20 14:02:35', 5);
INSERT INTO  `visita`(`id`, `dataorainizio`, `id_campagna`) VALUES (15, '2020-02-25 15:58:00', 5);
INSERT INTO  `visita`(`id`, `dataorainizio`, `id_campagna`) VALUES (16, '2020-02-21 15:59:00', 5);
INSERT INTO  `visita`(`id`, `dataorainizio`, `id_campagna`) VALUES (17, '2020-02-21 16:13:00', 5);
INSERT INTO  `visita`(`id`, `dataorainizio`, `id_campagna`) VALUES (22, '2020-03-12 15:11:00', 5);

我有这个问题

SELECT YEAR(v.dataorainizio) AS Year, WEEK(v.dataorainizio) AS Week, ROUND(sum(prezzo)/1000,2) as total_price
FROM visita_prodotto vp 
join visita v on vp.id_visita=v.id
where YEARWEEK(v.dataorainizio,1)<=YEARWEEK(NOW(),1) and  YEARWEEK(v.dataorainizio,1)>=YEARWEEK(NOW(),1)-10 and v.id_campagna=5
GROUP BY Year, Week
order by Week desc

产生这个结果的

YEAR    WEEK TOTAL_PRICE
2020    10   237,50
2020    8    4,50
2020    7    134,68
2020    6    20,00

我想要

YEAR    WEEK TOTAL_PRICE DIFFERENCE
2020    10   237,50        233
2020    8    4,50          -130,18
2020    7    134,68        114,68
2020    6    20,00

我试过sum(lead(，没有成功。。

相关表格结构

第一个表访问_点到

id      id_visita                 prezzo
int pk  int(related to visita)    int

联接表访问：

id     dataorainizio id_campagna
int pk datetime      int

我正在使用MARIADB 10.2

有人能帮我吗？

谢谢

WITH cte AS ( SELECT YEAR(v.dataorainizio) AS Year, 
WEEK(v.dataorainizio) AS Week, 
ROUND(sum(prezzo)/1000,2) as total_price
FROM visita_prodotto vp 
join visita v on vp.id_visita=v.id
where YEARWEEK(v.dataorainizio,1)<=YEARWEEK(NOW(),1) 
and YEARWEEK(v.dataorainizio,1)>=YEARWEEK(NOW(),1)-10 
and v.id_campagna=5
GROUP BY Year, Week )
SELECT `Year`, 
`Week`, 
total_price, 
total_price - LAG(total_price) OVER (ORDER BY `Year`, `Week`) difference
/* or  total_price - LEAD(total_price) OVER (ORDER BY `Year` DESC, `Week` DESC) difference */
FROM cte
ORDER BY Year DESC, Week DESC

小提琴

当然。您将需要一个子查询，如

SELECT YEAR(v.dataorainizio) AS Year, WEEK(v.dataorainizio) AS Week, ROUND(sum(prezzo)/1000,2) as total_price, t2.tp - ROUND(sum(prezzo)/1000,2) 
FROM visita_prodotto vp 
join visita v on vp.id_visita=v.id
join (
SELECT ROUND(sum(prezzo)/1000,2) as tp
FROM visita_prodotto vp2 
join visita v2 on vp2.id_visita=v2.id
where YEARWEEK(v2.dataorainizio,1)<=YEARWEEK(NOW(),1) and  YEARWEEK(v2.dataorainizio,1)>=YEARWEEK(NOW(),1)-10 and v2.id_campagna=5
GROUP BY YEAR(v2.dataorainizio), WEEK(v2.dataorainizio)
HAVING (YEAR(v.dataorainizio) > YEAR(v2.dataorainizio)) OR ((YEAR(v.dataorainizio) = YEAR(v2.dataorainizio)) AND (WEEK(v.dataorainizio) > WEEK(v2.dataorainizio)))
ORDER BY YEAR(v2.dataorainizio) desc, WEEK(v2.dataorainizio) desc
LIMIT 0, 1
) t2
where YEARWEEK(v.dataorainizio,1)<=YEARWEEK(NOW(),1) and  YEARWEEK(v.dataorainizio,1)>=YEARWEEK(NOW(),1)-10 and v.id_campagna=5
GROUP BY Year, Week, t2.tp
order by Week desc

子查询选择一个比当前组早的组，然后进行减法运算。我没有数据库示例。如果你还在挣扎，那么创建一个SQL Fiddle并在评论部分分享链接。

您可以像这样使用LEAD((窗口函数：

SELECT 
YEAR(v.dataorainizio) AS Year, 
WEEK(v.dataorainizio) AS Week, 
ROUND(SUM(prezzo)/1000,2) as total_price,
LEAD(ROUND(SUM(prezzo)/1000,2)) OVER (ORDER BY YEAR(v.dataorainizio), WEEK(v.dataorainizio)) - ROUND(SUM(prezzo)/1000,2) difference
FROM visita_prodotto vp 
JOIN visita v ON vp.id_visita=v.id
WHERE YEARWEEK(v.dataorainizio,1)<=YEARWEEK(NOW(),1) AND YEARWEEK(v.dataorainizio,1)>=YEARWEEK(NOW(),1)-10 AND v.id_campagna=5
GROUP BY Year, Week
ORDER BY Week desc

MariaDB似乎不支持在窗口函数中嵌套聚合函数，所以这样做：

WITH cte AS (
SELECT 
YEAR(v.dataorainizio) AS Year, 
WEEK(v.dataorainizio) AS Week, 
ROUND(SUM(prezzo)/1000,2) AS total_price,
ROW_NUMBER() OVER (ORDER BY YEAR(v.dataorainizio) DESC, WEEK(v.dataorainizio) DESC) AS rn
FROM visita_prodotto vp 
JOIN visita v ON vp.id_visita=v.id
WHERE YEARWEEK(v.dataorainizio,1)<=YEARWEEK(NOW(),1) AND YEARWEEK(v.dataorainizio,1)>=YEARWEEK(NOW(),1)-10 AND v.id_campagna=5
GROUP BY Year, Week
)
SELECT c1.Year, c1.Week, c1.total_price,
c1.total_price - c2.total_price AS difference
FROM cte c1 LEFT JOIN cte c2
ON c2.rn = c1.rn + 1
ORDER BY c1.Year DESC, c1.Week DESC

请参阅演示
结果：

> Year | Week | total_price | difference
> ---: | ---: | ----------: | ---------:
> 2020 |   10 |      237.50 |     233.00
> 2020 |    8 |        4.50 |    -130.18
> 2020 |    7 |      134.68 |     114.68
> 2020 |    6 |       20.00 |     null

仅适用于比您的MySQL旧的版本。。。

SELECT n.yw
, n.total
, ROUND(n.delta,2) delta 
FROM  
( SELECT a.* 
, @prev := total-@prev delta
, @prev := total 
FROM 
( SELECT DATE_FORMAT(dataorainizio,'%Y-%u') yw
, SUM(prezzo)/1000 total 
FROM visita v 
JOIN visita_prodotto vp 
ON vp.id_visita = v.id 
GROUP 
BY yw
) a
, (SELECT @prev:=null) vars ORDER BY yw
) n 
ORDER 
BY yw DESC;
+---------+----------+---------+
| yw      | total    | delta   |
+---------+----------+---------+
| 2020-11 | 237.4980 |  233.00 |
| 2020-09 |   4.5000 | -130.18 |
| 2020-08 | 134.6760 |  114.68 |
| 2020-07 |  20.0000 |    NULL |
+---------+----------+---------+

相关内容

最新更新

热门标签：