hive sql 即hql行转列和列转行经典例子，无需自建数据

最新推荐文章于 2024-12-05 18:05:57 发布

Fai_te

最新推荐文章于 2024-12-05 18:05:57 发布

阅读量265

点赞数

文章标签： hive

本文链接：https://blog.youkuaiyun.com/Fai_te/article/details/106834415

版权

本文深入解析了一段复杂的SQL查询代码，详细介绍了如何使用SQL进行数据筛选、聚合和连接操作。通过示例展示了如何利用子查询、连接(join)、分组(group by)、聚合函数和字符串函数来解决实际业务问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT table1.id
, concat_ws('|',collect_list(table1.max_or_min_part)) as max_and_min_part
from
(
SELECT tab1.id as id
, if(tab2.min_part = tab1.col2,concat('min_part=',tab1.order_id),concat('max_part=',tab1.order_id))
as max_or_min_part
from
(
SELECT id
,order_id
,split(order_id,':')[0] as col1
,split(order_id,':')[1] as col2
from
(SELECT '1' as id
,concat("aa:11",",bb:22",",cc:33") as text
) tb1
lateral view explode(split(tb1.text,',')) num as order_id
)tab1
left join
(
SELECT tb2.id as id
,min(tb2.col2) as min_part
,max(tb2.col2) as max_part
from
(
SELECT t2.id
,order_id
,split(order_id,':')[0] as col1
,split(order_id,':')[1] as col2
from
(SELECT '1' as id
,concat("aa:11",",bb:22",",cc:33") as text
) t2
lateral view explode(split(t2.text,',')) num as order_id
)tb2
group by tb2.id
) tab2
on tab1.id = tab2.id
where tab1.col2 = tab2.max_part
or tab1.col2 = tab2.min_part
) table1
group by table1.id
;