时间戳

首页 > 解决方案 > 每年的 COUNT 月销售额

sql-server - 每年的 COUNT 月销售额

问题描述

我需要按区域检查长期日期（5 年以上）的月销售额（计数而不是总和），类似于 excel 数据透视表，目前我正在使用 Pandas，但这里没有人使用它，所以我正在尝试在 SQLServer 中为任何需要它的人生成视图或存储过程。在此表中，销售额存储在与区域相关联的产品中。

我可以列出和分组区域、销售额、月份和年份，但正如我所提到的，如果垂直对齐的月份或年份（每年大约有 100k 条记录并且此时 excel 滞后），阅读起来会更容易。

CREATE TABLE SALESHS
(
    IDAREA INT,
    DATEREG [NVARCHAR](50) NOT NULL,
    IDPROD [NVARCHAR](50) NOT NULL
);
GO
-- Insert rows into table 'SALESHS'
INSERT INTO SALESHS
(
 IDAREA, DATEREG, IDPROD
)
VALUES
(
 1, '12/03/2019', 'xplpc'
),
(
 1, '15/03/2019', 'ndtlctm'
),
(
 2, '12/04/2019', 'wntd'
)
GO

SELECT IDAREA, 
    COUNT(IDAREA) AS CANT, 
    DATEREG, --DATE AS DD/MM/YYYY
    DATEPART(MM,CAST(DATEREG AS DATETIME)) AS MONTH, 
    DATEPART(YYYY,CAST(DATEREG AS DATETIME)) AS YEAR,
FROM saleshs
WHERE DATEREG > 201712
GROUP BY DATEREG , idarea
ORDER BY DATEREG

威奇返回这个：

IDAREA  AMOUNT  MONTH       YEAR    PER_PRO
----------------------------------------
1           2      03       2019    201904
2           1      04       2019    201904

预期成绩：

IDAREA JAN2019 FEB2019 MAR2019 APR2019
--------------------------------------
1            0       0       2       0
2            0       0       0       1

我知道 sql 的基础知识，我也不期望得到完整的答案，但是任何可以帮助我构建此视图的东西都值得赞赏。我也尝试过 PIVOT，但我无法在同一个查询中计算、区分和求和。

标签： sql-servertsqlsql-server-2008count

解决方案

--Build the column names for Pivot using dynamic SQL
DECLARE @YourChoice date
set @YourChoice = '2017/12/13' --change here to what date you want the earliest

declare @count int  = 0
declare @columnstr varchar(max)
declare @columnpivot varchar(max)
declare @onecolumnname varchar(20)

set @columnstr = ''
set @columnpivot = ''
set @onecolumnname = ''
while @count <= DATEDIFF(MONTH,@YourChoice,GETDATE())
begin
    set @onecolumnname = concat(cast(datename(month,dateadd(month,@count,@YourChoice)) as varchar(50)),cast(year(dateadd(month,@count,@YourChoice)) as varchar(10)))
    set @columnstr =  @columnstr + 'coalesce([' + @onecolumnname+ '],0) as '+@onecolumnname+', '
    set @columnpivot =  @columnpivot + '['+@onecolumnname+'], '
    set @count = @count + 1
end

set @columnstr = left(@columnstr,len(@columnstr)-1)
set @columnpivot = '('+left(@columnpivot,len(@columnpivot)-1)+')'

--Pivot time!
declare @str varchar(max)
set @str = 
'select IDAREA,' + @columnstr +' from (
select count(s.idarea) as amount,IDAREA,columnname from (
select *,datename(month,cast(substring(datereg,7,4)+''-''+substring(datereg,4,2)+''-''+substring(datereg,1,2) as datetime)) + SUBSTRING(datereg,7,4) as columnname
from SALESHS )s
group by IDAREA,columnname)s1
pivot
(
 max(s1.amount)
 for s1.columnname in '+@columnpivot+'
 ) p'
 exec (@str)

测试结果 1 ('2017/12/13'):

DB<>小提琴

测试结果 2 ('2018/12/14'):

DB<>小提琴

推荐阅读