我有一个简单的表,其中保存了某个组的日期和总销售额:
date | totalsales
=======================
2014-05-01 | 3000
2014-05-02 | 3100
2014-05-03 | 3500
2014-05-04 | 3650
我喜欢计算一些东西,比如:
- 每日销售量 平均销售
- 增长率%
结果应该看起来像(手工计算所以可能是错误的:))
date | sales | average | growth
=======================================
2014-05-01 | 0 | 0 | 0
2014-05-02 | 100 | 50 | 100
2014-05-03 | 400 | 166.66 | 400
2014-05-04 | 150 | 162.5 | 37.5
这甚至可能在sql语句或我应该计算与PHP或其他服务器软件?
假设每个日期都有自己唯一的行,您可以通过连接回原始表来实现,如下所示:
SELECT t1.Date, CASE WHEN t2.Date IS NULL THEN 0 ELSE (t1.totalsales - t2.totalsales)
END AS sales
FROM table t1
LEFT JOIN table t2 ON t2.Date = DATE_ADD(t1.Date, INTERVAL -1 DAY)
ORDER BY 1
这将至少提供您的第一列,并且您应该能够从那里计算出其余部分的数学。在CASE语句中使用左连接非常重要,否则将无法获得表(第一行)中最低的日期
如果每个日期没有得到自己唯一的行,这种方法仍然可行,您只需要在子查询中使用GROUP BY和SUM在日期列上创建您的数据集。
下面是每行没有子选择的完整查询:(感谢@nmarsh编写了最难的部分)
看到SQL小提琴: http://sqlfiddle.com/!2/be4654/34/0
SELECT
t1.Date,
CASE
WHEN t2.date IS NULL THEN 0 ELSE (t1.totalSales - t2.totalSales)
END AS sales,
CASE
WHEN t2.date IS NULL THEN 0 / (@curRow := @curRow + 1) ELSE ((@curSum := @curSum + (t1.totalSales - t2.totalSales)) / (@curRow := @curRow + 1))
END AS average,
CASE
WHEN t3.date IS NULL AND t2.date IS NULL THEN 0
WHEN t3.date IS NULL THEN (t1.totalSales - t2.totalSales)
WHEN t2.date IS NULL THEN 0 ELSE ((t1.totalSales - t2.totalSales) * 100) / (t2.totalSales - t3.totalSales)
END AS growth
FROM test t1
LEFT JOIN test t2 ON t2.date = DATE_ADD(t1.Date, INTERVAL -1 DAY)
LEFT JOIN test t3 ON t3.date = DATE_ADD(t2.Date, INTERVAL -1 DAY)
JOIN (SELECT @curRow := 0) r
JOIN (SELECT @curSum := 0) ct
ORDER BY 1;
原表
date | totalsales
=======================
2014-05-01 | 3000
2014-05-02 | 3100
2014-05-03 | 3500
2014-05-04 | 3650
date | sales | average | growth
=======================================
2014-05-01 | 0 | 0 | 0
2014-05-02 | 100 | 50 | 100
2014-05-03 | 400 | 166.66 | 400
2014-05-04 | 150 | 162.5 | 37.5
可以使用递归语句。在每次迭代中,计算一天的请求数据,并删除第一天(最老的)的数据。
你也可以用PHP来做,这似乎更好,因为你不想在MySQL表上放太多的负载,以防它不节省你的时间/计算。
对数字不太确定,但如果你能更具体地说明结果,我可以再检查一下。
您可以使用ROW_NUMBER()创建2个数据集,并在ROW_NUMBER()和ROW_NUMBER()-1上连接它们,以获得当前和先前值的偏移量,以计算增长。示例:
DECLARE @Data TABLE (SalesDate DATETIME, totalSales INT)
INSERT INTO @Data (SalesDate , totalSales) VALUES ('2014-05-01' , 3000)
INSERT INTO @Data (SalesDate , totalSales) VALUES ('2014-05-02' , 3100)
INSERT INTO @Data (SalesDate , totalSales) VALUES ('2014-05-03' , 3500)
INSERT INTO @Data (SalesDate , totalSales) VALUES ('2014-05-04' , 3650)
SELECT
CurrentDt.SalesDate
,ISNULL(CurrentDt.totalSales - PreviousDt.totalSales ,0) AS Sales
,FirstDate.FirstDate
, NULLIF(CAST((CurrentDt.SalesDate - FirstDate.FirstDate) AS INT)+1,0) AS SellingDays
,(ISNULL(CurrentDt.totalSales - PreviousDt.totalSales ,0))
/ NULLIF(CAST((CurrentDt.SalesDate - FirstDate.FirstDate) AS INT)+1,0) AS AverageSales
FROM
(SELECT Min(SalesDate) AS FirstDate FROM @Data) AS FirstDate,
/*Base Sales Data*/
(
SELECT
ROW_NUMBER() OVER(ORDER BY SalesDate) AS RowNum
,SalesDate
,totalSales
FROM
@Data
) AS CurrentDt
/*Previous Value for Growth*/
LEFT JOIN
(
SELECT
ROW_NUMBER() OVER(ORDER BY SalesDate) AS RowNum
,SalesDate
,totalSales
FROM
@Data
) AS PreviousDt
ON CurrentDt.RowNum -1 = PreviousDt.RowNum
我已经使用了MSSQL,但是MySQL支持ROW_NUMBER OVER.
希望这个查询对你有帮助
SELECT
sample.id,
sample.date AS oggi,
sample.value AS sales,
((SELECT SUM(sample.value) FROM sample WHERE sample.date <= oggi ) / (SELECT COUNT(sample.value) FROM sample WHERE sample.date <= oggi ) ) AS avarege,
sample.value / IF((SELECT sample.value FROM sample WHERE sample.date = (oggi - INTERVAL 1 DAY )) = 0,sample.value,(SELECT sample.value FROM sample WHERE sample.date = (oggi - INTERVAL 1 DAY ))) *100 AS 'growt-percent'
-- (SELECT SUM(sample.value) FROM sample WHERE sample.date <= oggi ) AS somma,
-- (SELECT count(sample.value) FROM sample WHERE sample.date <= oggi ) AS conta,
-- (SELECT sample.value FROM sample WHERE sample.date = (oggi - INTERVAL 1 DAY )) as valoreieri,
FROM sample
WHERE sample.date BETWEEN '2014-05-01 00:00:00' AND '2014-05-31 00:00:00'
table data is
结果id date value
1 2014-05-01 00:00:00 0 2 2014-05-02 00:00:00 100 3 2014-05-03 00:00:00 400 4 2014-05-04 00:00:00 150 5 2014-05-05 00:00:00 200
id oggi销售平均增长率-百分比
1 2014-05-01 00:00:00 0 0.0000 (NULL) 2 2014-05-02 00:00:00 100 50.0000 100.0000 3 2014-05-03 00:00:00 400 166.6667 400.0000 4 2014-05-04 00:00:00 150 162.5000 37.5000 5 2014-05-05 00:00:00 200 170.0000 133.3333
注意,我使用的datetime字段不仅仅是日期如果您对查询有疑问,请询问
对不起,我的英语不好
编辑最后3行是注释的,因为我只将其用于test