一张表中有店名 订单量 统计月度的订单量 月同比 月环比 以及保留订单销售店店名 sql语句
时间: 2024-03-09 07:50:28 浏览: 12
假设表名为`sales_data`,字段为`store_name`、`order_quantity`、`statistics_month`,则可以使用如下SQL语句计算月度订单量、月同比、月环比和保留订单销售店店名:
```
SELECT
store_name,
SUM(order_quantity) AS monthly_order_quantity,
(SUM(order_quantity) - LAG(SUM(order_quantity), 12) OVER(PARTITION BY store_name ORDER BY statistics_month)) / LAG(SUM(order_quantity), 12) OVER(PARTITION BY store_name ORDER BY statistics_month) * 100 AS month_year_on_year_growth_rate,
(SUM(order_quantity) - LAG(SUM(order_quantity), 1) OVER(PARTITION BY store_name ORDER BY statistics_month)) / LAG(SUM(order_quantity), 1) OVER(PARTITION BY store_name ORDER BY statistics_month) * 100 AS month_on_month_growth_rate,
STRING_AGG(DISTINCT store_name, ',') WITHIN GROUP (ORDER BY store_name) AS reserved_store_names
FROM
sales_data
GROUP BY
store_name,
statistics_month;
```
其中,`SUM(order_quantity)`用于计算月度订单量,`LAG(SUM(order_quantity), 12) OVER(PARTITION BY store_name ORDER BY statistics_month)`用于计算去年同月的订单量,`LAG(SUM(order_quantity), 1) OVER(PARTITION BY store_name ORDER BY statistics_month)`用于计算上个月的订单量,从而计算月同比和月环比。`STRING_AGG(DISTINCT store_name, ',') WITHIN GROUP (ORDER BY store_name)`用于将保留订单销售店的店名拼接成字符串。