SQL语句计算销售额同比

考虑一个常见的业务场景：某零售商想要分析每月各个省份各个渠道维度下品牌的销售额同比指标。

销售额同比 =（本月销售额-去年同月销售额）/ 去年同月销售额

例如，农夫山泉202201销售额同比 =（农夫山泉202201销售额-农夫山泉202101销售额）/ 农夫山泉202101销售额

例如，sales 表格如下：

从上述表格中，我们可以看到包装水品类各品牌包含的商品每月在各个省份各个渠道的销量和销售额信息。

现在我们使用SQL来计算每个品牌在每月各个省份各个渠道对应的销售额同比指标。

方法一：字符串拆分获取年份和月份

从上述sales表中我们可以看出month字段是一个由8位数字组成的字符串，计算同比指标我们需要从month中获取品牌对应的年份和月份的信息。这里我们使用substr(month,1,4)函数从month中截取前四位数字字符，即为年份；同理使用substr(month,5,2)函数从month中获取第5和6个字符，即为月份。

语法：substr（str，pos，len）：返回从指定位置开始，指定长的的字符串

        str：为列名/字符串；

        pos：字符起始位置，从1开始；

        len：为截取字符个数/长度。

计算销售额同比详细SQL如下图代码所示，a.amout为今年销售额，b.amout为去年同月销售额。重要的表关联条件为SUBSTR(a.month,1,4) -1 = SUBSTR(b.month,1,4)，即a的年份为今年，b的年份为去年，也就是说a与b的年份相差一年，且a与b的月份要相同，条件为SUBSTR(a.month,5,2)  = SUBSTR(b.month,5,2)。

此外，在使用销售额同比公式计算指标时我们可以使用case when 或者If来判断一下分母为0时的情况，否则当分母为0时，sql会报错。

--方法一：
with tmp as (
select     month	
	      ,province_name	
	      ,channel
		  ,category	
	      ,brand_name		
	      ,sum(amount) amount 
from sales
group by month	
	      	,province_name	
	      	,channel	
		  	,category
	      	,brand_name
) select   a.month	
	      ,a.province_name	
	      ,a.channel
		  ,a.category	
	      ,a.brand_name
		  ,case when b.amount is null then 0 else (a.amount-b.amount)/b.amount end as  year_to_year_growth_1
          ,IF(b.amount is null,0,(a.amount-b.amount)/b.amount) as year_to_year_growth_2
	from tmp a 
	left join tmp b  
	on SUBSTR(a.month,1,4) -1 = SUBSTR(b.month,1,4)
	and SUBSTR(a.month,5,2)  = SUBSTR(b.month,5,2)
	and a.province_name = b.province_name 
	and a.channel = b.channel
	and a.category = b.category
	and a.brand_name = b.brand_name
;

SQL运行后，数据如下：

从数据结果，我们可以看到2022年01月今麦郎上海市便利店渠道的销售额同比为66.4%。

方法二：日期函数处理关联条件

除了上述从month字符串中拆分年份和月份的方法外，我们还可以使用日期函数来获取年份和月份信息。日期month的数据类型为字符串，使用日期函数前我们需要先使用to_date() 函数将字符串转换为日期格式，然后使用dateadd获取去年对应的日期。如下图所示表关联条件为 to_char(dateadd(to_date(a.month,'yyyymmdd'),-1,'yyyy'),'yyyymmdd')  = b.month

语法：dateadd（date，number，datepart）：在日期中添加或减去指定的时间单位。

        date：合法日期表达式，格式可以是date，datetime或者timestamp

        number：指定时间单位的增减，正值为增，负值为减。

        datepart：指定时间单位，可以为年、月或者天，即yyyy表示年，mm表示月，dd表示天。

代码详情如下所示：

--方法二
with tmp as (
select     month	
	      ,province_name	
	      ,channel
		  ,category	
	      ,brand_name		
	      ,sum(amount) amount 
from sales
group by month	
	      	,province_name	
	      	,channel	
		  	,category
	      	,brand_name
) select   a.month	
	      ,a.province_name	
	      ,a.channel
		  ,a.category
	      ,a.brand_name
		  ,case when b.amount is null then 0 else (a.amount-b.amount)/b.amount end as year_to_year_growth
	from tmp a 
	left join tmp b  
	on  to_char(dateadd(to_date(a.month,'yyyymmdd'),-1,'yyyy'),'yyyymmdd')  =b.month
	and a.province_name = b.province_name 
	and a.channel = b.channel
	and a.category = b.category  
	and a.brand_name = b.brand_name
;

注：不同的数据对应的函数语法会有一定差异，语法以使用数据库标准为准。