可视化总结：

愿为卿懒回顾

已于 2024-01-12 10:13:06 修改

阅读量1k

点赞数 27

文章标签： sql 数据仓库

于 2024-01-12 10:03:51 首次发布

本文链接：https://blog.csdn.net/m0_53678557/article/details/135544493

版权

目前做了可视化几个月了，目前个人认为里可视化没有什么难的点，多数难点在于对于平台的熟练度和对sql变形处理以适配可视化中图表的数据形式要求，这篇主要总结我再前端中常用sql变形语法

最常见的列转行问题

例如：

2019年支出 2020年支出 2021年支出 2022年支出总支出

300万 300万 300万 300万 1200万

转换成

2019年支出 300万

2020年支出 300万

2021年支出 300万

2022年支出 300万

总支出 1200万

语法为

SELECT 
id, col_name, col_value 
FROM 
( SELECT 
id, CONCAT_WS(',', 2019年支出,2020年支出,2021年支出,2022年支出,总支出) AS concatenated_names
FROM table_name ) t 
LATERAL VIEW EXPLODE(SPLIT(t.concatenated_names, ',')) exploded AS col_value 
LATERAL VIEW EXPLODE(SPLIT('2019年支出,2020年支出,2021年支出,2022年支出,总支出', ',')) exploded_names AS col_name;

或者

SELECT ‘2019年支出’,2019年支出 FROM table
 UNION ALL
SELECT ‘2020年支出’,2020年支出 FROM table 
UNION ALL 
SELECT ‘2021年支出’,2021年支出 FROM table 
UNION ALL 
SELECT ‘2022年支出’,2022年支出 FROM table 
UNION ALL 
SELECT ‘总支出’,总支出 FROM table;

两者都可实现

行转列语法(一瞬间就想到case when)

SELECT
id, 
MAX(CASE WHEN col_name = 'name1' THEN col_value END) AS name1, 
MAX(CASE WHEN col_name = 'name2' THEN col_value END) AS name2, 
MAX(CASE WHEN col_name = 'name3' THEN col_value END) AS name3 
FROM table_name GROUP BY id;

Mysql语法----Groupconcat ----用于将多条记录中的某一列合并成一个字符串。常用于需要将多个值拼接成一个字符串的场景

他的作用跟hive语法----later view explode（split（））作用恰恰相反

user_id	order_id
1001	1
1001	2
1002	3
1003	4

例如数据如图所示：经过sql：

SELECT 
user_id, group_concat(order_id) as order_list 
FROM order_info GROUP BY user_id;

后变成：

user_id	order_list
1001	1,2
1002	3
1003	4

我们使用later view explode（split（））就可以恢复原样

我们使用场景为一张表中记录项目每年的收入，表的字段为项目年份收入，然后我们需要把每个项目每年的收入列转行，我们的想法就是使用-Groupconcat，然后再使用SUBSTRING_INDEX('apple,banana,orange', ',', 1)，取出每一个年份值重命名就可以

REPEAT（这个函数真的冷门，但是很好用）

REPEAT 函数是 SQL 中用于重复指定字符串多次的函数。它接受两个参数：要重复的字符串和重复次数。

REPEAT(string, count)

例如你可以利用sql得出1+1+1+1=4 ，怎么让sql实现4=1+1+1+1呢

CONCAT(REPEAT('1+', 4-1), '1'),

举例：有一个题目求每年在校人数

A B C

2018 3 2000

2019 4 1000

2020 3 2000

2020 4 1000

第A列数据为年份，B为学生在校年数，C是当年此年和不同年限下招收人数，（B就是年限）

求每年在校人数

Select A,sum(c) from(

Select *,row_number() over(partition by A,B) as ac from (

Select A,B,C, col_value from (

Select 
A,CONCAT(REPEAT('1,', B-1), '1') as con,C from table) as a 
later view explode(split(con,”,”)) exploded AS col_value) as bb ) as AA 
group by A+ac-1,B

但是他的妙用不止在这里，例如我们之前有一个需求为我们把每个项目每年的年份支出都放在一个字段里面，数据大概为这样（2019支出，2020支出，2021支出。。。。），但是会存在一个问题，有的项目可能从2019开始，有的项目可能从2023年开始，这样容易导致后面对数据处理时候，逻辑混乱，所以我们要统一开始时间，把时间进行为0处理，因为我们所有的项目最早年份为2017，所以我们的处理手段为用（当前时间-2016）-（项目存在年份时长）（项目存在年份时长：比如项目从2020年开始到现在的年份个数，那就是2020，2021，2022，2023，2024，5年），所以应该是concat_ws(repeat（“0，”，2024-2016-5),数据)

Repeat语法在hive中代表是lpad(str, len, pad)

str：要填充的字符串。

len：填充后的长度，如果原始字符串长度超过此长度则返回原始字符串。

pad：用作填充的字符。

以下是示例：

SELECT lpad('Hello', 10, '*');

该查询将返回一个长度为 10 的字符串，左侧填充了 5 个星号（*），结果为 "*****Hello"。

With as():

WITH alias_name AS ( 
SELECT column1, column2, ... FROM table_name WHERE condition ) 
SELECT column1, column2, ... FROM alias_name WHERE condition;

With as的存在类似于一个视图，但是在可视化开发的时候，模型基本已经确认，很难再增加表或者其他视图，维护很麻烦，会涉及后期维护问题，但是with as的存在不需要建表就能直接使用，当比如当sql语法有大量重复片段

曾经有一个需求为代码优化：问题所在就是代码中多数情况下字段和其他选项相同，只是命名会根据过滤条件的不同而不同，所以导致不停对同一张表访问，导致资源紧张和查询缓慢，大佬给的推荐意见就是with as,在根据业务调整后，整体效果提升显著。

Left join和right join

这两个语法之前连接我都想到了，但是后来工作中我常常发现这两个的函数的另一个作用是验证数据，例如我们现在数据上云或者数据转移后怎么确保我们的数据正确呢，我们在数据中台阶段很多时候梳理数据，只是把业务重新整理理了一遍，但是数据和值本身并没有改变，但是会因为人理解或者网络原因，会常常丢失一些数据，所以常常需要验证数据完整性。除了使用count比较数据数量，然后就是把源数据短暂抽出，然后进行left join，一般都是把源数据放在左边，然后我们数据放在右边，最后count空值数量，（能用唯一值就用唯一值），如果不能使用唯一值，可以考虑使用with a1 as（源表），a2 as(我们提取表)先把我们的要连接的字段进行一次聚合，比如对其他字段进行count，如果是金额可以考虑sum，然后比较，先判断空，最后再比较金额或者数量（这种方便更稳妥）

例子：一般先判断有没有空值，然后再比较数值

with tb1 as (
select 
oms_rtn_ord_num,count(oms_rtn_ord_num) as ccc,sum(rtl_rtn_amt) as c1 
from dwd_trd_online_return_detail_di_r3_ret_di 
where dt='2023-10-21' group by oms_rtn_ord_num order by oms_rtn_ord_num
),tb2 as (
select 
return_order_id,count(return_order_id) as cc,sum(share_payment) as c2 
from sm_ods.init_dw_order_return_in_d 
where dt='2023-10-21' group by return_order_id order by return_order_id
)
select  t1.*,t2.*
from tb1 t1 
left  join tb2 t2 on t1.oms_rtn_ord_num=t2.return_order_id
where t2.cc is null