请教SQL写数据透视表，用于数据分析

Question

问题遇到的现象和发生背景

希望能用SQL写一个数据透视图分析数据

我想要达到的结果，如果你需要快速回答，请尝试 “付费悬赏”

Answer 1

MSSQL的行转列

CREATE TABLE TMP(EA_msgtime DATETIME,EA_Re_Name VARCHAR(20))
insert into tmp  (EA_msgtime,EA_Re_Name) VALUES ('2023-1-11 12:47:20.000','测试点A') 
insert into tmp  (EA_msgtime,EA_Re_Name) VALUES ('2023-1-11 11:47:32.000','测试点B')
insert into tmp  (EA_msgtime,EA_Re_Name) VALUES ('2023-1-11 11:47:29.000','测试点A')
insert into tmp  (EA_msgtime,EA_Re_Name) VALUES ('2023-1-11 11:35:19.000','测试点B')
insert into tmp  (EA_msgtime,EA_Re_Name) VALUES ('2023-1-9 15:19:48.000','测试点A')
insert into tmp  (EA_msgtime,EA_Re_Name) VALUES ('2023-1-9 15:18:49.000','测试点B') 
insert into tmp  (EA_msgtime,EA_Re_Name) VALUES ('2023-1-9 15:18:14.000','测试点A')
insert into tmp  (EA_msgtime,EA_Re_Name) VALUES ('2022-11-24 12:47:20.000','测试点C')

--如果测试点是死的，可以如下
SELECT CONVERT(VARCHAR(10),EA_msgtime,120) AS EA_msgtime,
       SUM(CASE WHEN EA_Re_Name='测试点A' THEN 1 ELSE 0 END) 测试点A,
       SUM(CASE WHEN EA_Re_Name='测试点B' THEN 1 ELSE 0 END) 测试点B,
       SUM(CASE WHEN EA_Re_Name='测试点C' THEN 1 ELSE 0 END) 测试点C,
       COUNT(1) '总计'
FROM tmp
GROUP BY CONVERT(VARCHAR(10),EA_msgtime,120)

--如果测试点不固定，则用动态sql

DECLARE @sql VARCHAR(8000)
DECLARE @field VARCHAR(1000)

SET @field= ''
SELECT @field = @field +','+ 'SUM(CASE WHEN EA_Re_Name='''+EA_Re_Name+''' THEN 1 ELSE 0 END) AS '''+EA_Re_Name+'''' FROM tmp GROUP BY EA_Re_Name 

SELECT @sql = 'SELECT CONVERT(VARCHAR(10),EA_msgtime,120) AS EA_msgtime '+@field +',count(1) ''总计'' FROM tmp GROUP BY CONVERT(VARCHAR(10),EA_msgtime,120)'
EXEC(@SQL)
--result
EA_msgtime    测试点A    测试点B    测试点C    总计
2022-11-24    0    0    1    1
2023-01-09    2    1    0    3
2023-01-11    2    2    0    4

Answer 2

发一下你的建表DDL 和数据insert语句

Answer 3

SELECT 
    EA_msgtime,
    SUM(IF(EA_Re_name = '测试点A', 1, 0)) AS '测试点A',
    SUM(IF(EA_Re_name = '测试点B', 1, 0)) AS '测试点B',
    SUM(IF(EA_Re_name = '测试点C', 1, 0)) AS '测试点C',
    count(1) as '总计'
FROM
    (SELECT 
        DATE(EA_msgtime) EA_msgtime, EA_Re_name
    FROM
        csdn_tb01) t
GROUP BY EA_msgtime

Answer 4

SQL中使用GROUP BY子句和PIVOT/UNPIVOT操作来实现数据透视表。

基本语法：

SELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY column1, column2

其中aggregate_function可以是SUM, COUNT, AVG等。

例如，要按销售员和商品类别统计销售额，可以使用以下语句：

SELECT salesperson, category, SUM(sales)
FROM sales_table
GROUP BY salesperson, category

数据透视表可以通过PIVOT操作将行转换为列。

语法：

SELECT *
FROM table_name
PIVOT (aggregate_function(column_to_aggregate)
FOR column_to_rotate
IN (list_of_values))

例如，要将上面的统计结果转换为以商品类别为行，销售员为列的数据透视表，可以使用以下语句：

SELECT *
FROM (
    SELECT salesperson, category, SUM(sales)
    FROM sales_table
    GROUP BY salesperson, category
)
PIVOT (SUM(sales)
FOR salesperson
IN ( 'John', 'Mike', 'Bob'))

如果你的数据库不支持pivot 语法，还可以使用CASE 和GROUP BY来实现。