时间戳

首页 > 解决方案 > SQL 查询以获取以折扣价出售的商品的每个类别的每月销售数据

sql - SQL 查询以获取以折扣价出售的商品的每个类别的每月销售数据

问题描述

我打算准备一个 SQL 查询，以折扣价执行每个类别的月度销售。

这是我在SQL Server中的示例表结构（仅显示所需的列）

物品表：

物品编号 子类别 ID 购买日期 购买类型
1234 11 2021 年 1 月 22 日 打折
3456 12 2021 年 1 月 22 日 满的
5678 13 2021 年 2 月 10 日 打折
6788 12 01-01-2021 打折

子类别表

子类别 ID 子类别名称
11 移动的
12 笔记本电脑
13 T恤
14 植物

类别表

类别ID 分类名称
1 电子产品
2 服装
3 装饰

类别映射表

类别ID 子类别 ID
1 11
1 12
2 13
3 14

到目前为止，我已经准备了一个 SQL 查询来以折扣率执行每个类别的销售，如下所示：

select
CategoryName,
Count(*) as 'Count of Total Items',
count(case when PurchaseType = 'Discounted' then 1 else null end)  as 'Count of Discounted Items'
from
 (select
i.ItemID,
i.PurchaseDate,
i.PurchaseType AS PurchaseType,
c.CategoryName AS CategoryName
from 
Items i
LEFT Join SubCategory sc with(nolock) on sc.SubCategoryID = i.SubCategoryID
LEFT Join CategoryMapping cm with(nolock) on cm.SubCategoryID  = i.SubCategoryID
LEFT Join Category c with(nolock) on c.CategoryID = cm.CategoryID     

where i.PurchaseDate > '01/01/2021' and i.PurchaseDate < '12/31/2021'
)as Data
group by CategoryName

此查询工作正常。

现在我需要进一步修改此查询以获取每个CategoryNames的月销售额。

因此，在这种情况下，需要使用PurchaseDate列进行分组，但我无法弄清楚以最佳方式调整上述查询。

上面的当前 SQL 基于以下给定日期范围之间的类别分组进行计算（将始终限于给定的单一年份，而不跨越另一年） i.PurchaseDate > '01/01/2021' 和 i.PurchaseDate < '2021 年 12 月 31 日'

现在的要求是根据购买日期的月份进一步细分每个类别分组的销售额。

最终结果必须如下所示：

分类名称 月 如果总项目计数 折扣商品数量 每月折扣商品数量
电子产品 简 3 2 2
服装 二月 1 1 1
装饰 行进 0 0 0

如何通过进一步调整现有查询来获取月度销售数据

标签： sqlsql-server

解决方案

要在没有结果的月份中获取零行，请使用日历表，并将所有其他结果留给它。

通常人们会使用一个适当的日历表，但在这种情况下，总是正好 12 个月，所以你可以只使用一个虚拟VALUES表

SELECT
    CategoryName,
    EOMONTH(MIN(i.PurchaseDate)) AS Month,
    COUNT(*) AS [Count of Total Items],
    COUNT(CASE WHEN PurchaseType = 'Discounted' THEN 1 END) AS [Count of Discounted Items Per Month],
    SUM(
      COUNT(CASE WHEN PurchaseType = 'Discounted' THEN 1 END)
      ) OVER (PARTITION BY c.CategoryID) AS [Total Count of Discounted Items]
FROM (VALUES
    (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)
) AS v(Month)
LEFT JOIN Items i
    JOIN CategoryMapping cm on cm.SubCategoryID  = i.SubCategoryID
    JOIN Category c on c.CategoryID = cm.CategoryID
  ON MONTH(i.PurchaseDate) = v.Month
    AND i.PurchaseDate >= '20210101' and i.PurchaseDate < '20220101'
GROUP BY
  c.CategoryID,
  c.CategoryName;

您可能要考虑GROUPING SETS而不是SUM窗口函数。

笔记：

  • NOLOCK除非您真的知道自己在做什么，否则永远不要使用，这可能会导致错误的结果
  • 使用内连接而不是左连接，除非您想包含第二个表中不匹配的结果
  • 使用明确的日期格式，例如'20210101
  • 对日期使用半开间隔：包含的开始日期和不包含的结束日期
  • 用于[]引用列名，而不是''
  • 如果可能，请始终按唯一标识分组的列进行分组

推荐阅读