行转列,需要公司近三十天的销售量(30列)
问题遇到的现象和发生背景
现在我有一张表,其中包含了和订单相关的数据,包括公司key，公司每天的销售额，日期
希望可以看到每个公司近三十天每一天的销量，每个公司单独显示（也就是说我用A公司的账号登录，就只显示A公司的近三十天销量，每天销量单独显示，当日无订单，即为0）
（需做LEFT JOIN与其他表相关联，可使用company_key和sku_key关联上)
问题相关代码，请勿粘贴截图
SELECT
t.company_key,
t.spu_key,
t.sku_key
--近1天销量
--近2天销量
--...
--近30天销量
FROM t
LEFT JOIN tblB
ON t.company_key = tblB.company_key AND t.sku_key = tblB.sku_key
--其中tblB是包含订单相关数据的表
运行结果及报错内容
无
我的解答思路和尝试过的方法
无
我想要达到的结果
希望可以看到每个公司近三十天每一天的销量，每个公司单独显示（也就是说我用A公司的账号登录，就只显示A公司的近三十天销量，每天销量单独显示，当日无订单，即为0）
可以拿一点真实结构，或者相似的表模型贴出来。
SELECT company_key,
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 0 THEN sum ELSE 0 END) AS '第1天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 1 THEN sum ELSE 0 END) AS '往前第2天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 2 THEN sum ELSE 0 END) AS '往前第3天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 3 THEN sum ELSE 0 END) AS '往前第4天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 4 THEN sum ELSE 0 END) AS '往前第5天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 5 THEN sum ELSE 0 END) AS '往前第6天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 6 THEN sum ELSE 0 END) AS '往前第7天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 7 THEN sum ELSE 0 END) AS '往前第8天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 8 THEN sum ELSE 0 END) AS '往前第9天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 9 THEN sum ELSE 0 END) AS '往前第10天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 10 THEN sum ELSE 0 END) AS '往前第11天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 11 THEN sum ELSE 0 END) AS '往前第12天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 12 THEN sum ELSE 0 END) AS '往前第13天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 13 THEN sum ELSE 0 END) AS '往前第14天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 14 THEN sum ELSE 0 END) AS '往前第15天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 15 THEN sum ELSE 0 END) AS '往前第16天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 16 THEN sum ELSE 0 END) AS '往前第17天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 17 THEN sum ELSE 0 END) AS '往前第18天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 18 THEN sum ELSE 0 END) AS '往前第19天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 19 THEN sum ELSE 0 END) AS '往前第20天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 20 THEN sum ELSE 0 END) AS '往前第21天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 21 THEN sum ELSE 0 END) AS '往前第22天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 22 THEN sum ELSE 0 END) AS '往前第23天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 23 THEN sum ELSE 0 END) AS '往前第24天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 24 THEN sum ELSE 0 END) AS '往前第25天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 25 THEN sum ELSE 0 END) AS '往前第26天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 26 THEN sum ELSE 0 END) AS '往前第27天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 27 THEN sum ELSE 0 END) AS '往前第28天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 28 THEN sum ELSE 0 END) AS '往前第29天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 29 THEN sum ELSE 0 END) AS '往前第30天'
FROM t
WHERE company_key = '';-- 这里用登陆人的公司key
还有一个问题，你是需要累加的三十天还是每天的。这个需求没说清楚。
上面的sql 只是单表的
子查询先把捞出近三十天数据，然后外层使用窗口函数分区排序聚合
sum(sum) over(partition by company_key order by action_date rows between
UNBOUNDED PRECEDING and current row )
请尝试使用如下代码解决该问题：
SELECT
t.company_key
,t.spu_key
,t.sku_key
,b.商品的其他信息...
,sum(case when DATE_FORMAT(date_add(now(),interval -1 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近1天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -2 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近2天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -3 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近3天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -4 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近4天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -5 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近5天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -6 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近6天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -7 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近7天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -8 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近8天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -9 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近9天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -10 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近10天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -11 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近11天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -12 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近12天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -13 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近13天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -14 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近14天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -15 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近15天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -16 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近16天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -17 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近17天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -18 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近18天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -19 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近19天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -20 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近20天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -21 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近21天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -22 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近22天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -23 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近23天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -24 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近24天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -25 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近25天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -26 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近26天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -27 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近27天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -28 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近28天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -29 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近29天销量
,sum(case when DATE_FORMAT(date_add(now(),interval -30 day),'%Y-%m-%d') = DATE_FORMAT(action_date,'%Y-%m-%d') then sale_count[表中的sku销售数,需要改成表里面的字段] else 0 end) sale_count_day1 --近30天销量
FROM tabA t
LEFT JOIN tblB b
ON t.company_key = b.company_key AND t.sku_key = b.sku_key
where t.company_key = ? [这个是后端传入的company_key值，用来确认公司查看数据的权限]
group by t.company_key
,t.spu_key
,t.sku_key
,b.商品的其他信息...
希望可以帮助到你。
CREATE PROCEDURE P__company_Query
@BeginDT DATETIME=NULL,
@EndDT DATETIME=NULL,
WITH ENCRYPTION
AS
BEGIN
IF @BeginDT IS NULL
SET @BeginDT = '1900-01-01'
SET @BeginDT = CONVERT(NCHAR(11),@BeginDT ,120) + '00:00:00'
IF @EndDT IS NULL
SET @EndDT = GETDATE()
SET @EndDT = CONVERT(NCHAR(11),@EndDT ,120) + ' 23:59:59:998'
SELECT
t.company_key,
t.spu_key,
t.sku_key
--近1天销量
--近2天销量
--...
--近30天销量
FROM t
LEFT JOIN tblB
ON t.company_key = tblB.company_key AND t.sku_key = tblB.sku_key
where ......
select distinct top 32 [name],min(group里不包括的字段) from tbl_news group by 所有字段罗列上 order by add_date desc
在hive里,可以使用map和collect_list来实现pivot表格的效果
--测试数据
create table test_20220209(company_key int,su int,action_date varchar(8) );
insert into test_20220209 values (1,1,'20220101');
insert into test_20220209 values (1,2,'20220102');
insert into test_20220209 values (1,3,'20220103');
insert into test_20220209 values (1,4,'20220104');
insert into test_20220209 values (1,5,'20220105');
insert into test_20220209 values (1,6,'20220106');
insert into test_20220209 values (1,7,'20220107');
insert into test_20220209 values (1,8,'20220108');
insert into test_20220209 values (1,9,'20220109');
insert into test_20220209 values (1,10,'20220110');
insert into test_20220209 values (2,10,'20220101');
insert into test_20220209 values (2,9,'20220102');
insert into test_20220209 values (2,8,'20220103');
insert into test_20220209 values (2,7,'20220104');
insert into test_20220209 values (2,6,'20220105');
insert into test_20220209 values (2,5,'20220106');
insert into test_20220209 values (2,4,'20220107');
insert into test_20220209 values (2,3,'20220108');
insert into test_20220209 values (2,2,'20220109');
insert into test_20220209 values (2,1,'20220110');
--查询sql
SELECT
m.company_key,
COLLECT_LIST(g["20220101"])[0] AS d20220101,
COLLECT_LIST(g["20220102"])[0] AS d20220102,
COLLECT_LIST(g["20220103"])[0] AS d20220103,
COLLECT_LIST(g["20220104"])[0] AS d20220104,
COLLECT_LIST(g["20220105"])[0] AS d20220105,
COLLECT_LIST(g["20220106"])[0] AS d20220106,
COLLECT_LIST(g["20220107"])[0] AS d20220107,
COLLECT_LIST(g["20220108"])[0] AS d20220108,
COLLECT_LIST(g["20220109"])[0] AS d20220109,
COLLECT_LIST(g["20220110"])[0] AS d20220110
FROM
(
SELECT
company_key,
map(action_date,su) AS g
FROM
test_20220209
) m
GROUP BY company_key
测试效果
这种写法比case when还是要简洁一点
题主，可以考虑一下，将公司的数据查出来，再通过代码进行处理分组，返回到list到前端去。这样解决的方式，对数据库的压力也小些
key:公司名；sum：数据；date:日期
要查几天、那个公司 ，在where 中加条件即可
SELECT t.key,
t.Date,DATEDIFF(now(),t.date) as lastCount,
(SELECT Sum(p.Sum) from test p where p.Date>=t.date&&p.key=t.key) as Sum
from test t WHERE t.Date>=(date_add(now(),INTERVAL -3 day)) GROUP BY t.key,t.Date;
case when 配合sum