sql题--判断连续问题

最新推荐文章于 2024-06-11 23:07:34 发布

靓仔写sql

最新推荐文章于 2024-06-11 23:07:34 发布

阅读量389

点赞数 1

文章标签： sql 数据库

本文链接：https://blog.csdn.net/qq_42402817/article/details/131807078

版权

题目

在这里插入图片描述

A–>B实现：

-- impala
with data_ as ( 
  select 1 as id, 'aa' as name  
  union all 
  select 2 as id, 'aa' as name  
  union all 
  select 3 as id, 'aa' as name  
  union all 
  select 4 as id, 'd' as name   
  union all 
  select 5 as id, 'c' as name   
  union all 
  select 6 as id, 'aa' as name  
  union all 
  select 7 as id, 'aa' as name  
  union all 
  select 8 as id, 'e' as name   
  union all 
  select 9 as id, 'f' as name   
  union all     
  select 10 as id, 'g' as name  
)   
select
max(id) as id,
group_concat(name,'|') name_
from data_
group by 
name

-- hive
with data_ as ( 
  select 1 as id, 'aa' as name  
  union all 
  select 2 as id, 'aa' as name  
  union all 
  select 3 as id, 'aa' as name  
  union all 
  select 4 as id, 'd' as name   
  union all 
  select 5 as id, 'c' as name   
  union all 
  select 6 as id, 'aa' as name  
  union all 
  select 7 as id, 'aa' as name  
  union all 
  select 8 as id, 'e' as name   
  union all 
  select 9 as id, 'f' as name   
  union all     
  select 10 as id, 'g' as name  
)   
select
max(id) as id,
concat_ws('|', collect_list(name)) name_
from data_
group by 
name

A–>C实现：

--impala
with data_ as (
  select 1 as id, 'aa' as name
  union all
  select 2 as id, 'aa' as name
  union all
  select 3 as id, 'aa' as name
  union all
  select 4 as id, 'd' as name
  union all
  select 5 as id, 'c' as name
  union all
  select 6 as id, 'aa' as name
  union all
  select 7 as id, 'aa' as name
  union all
  select 8 as id, 'e' as name
  union all
  select 9 as id, 'f' as name
  union all 
  select 10 as id, 'g' as name 
)
SELECT
  max(id) as id,
 group_concat(name,'|') AS name_
FROM
  (
    SELECT
      id,
      name,
     (id - row_number() OVER (PARTITION BY name ORDER BY id)) AS grp
    FROM
      data_
  ) subquery
GROUP BY
  grp


-- hive 
with data_ as (
  select 1 as id, 'aa' as name
  union all
  select 2 as id, 'aa' as name
  union all
  select 3 as id, 'aa' as name
  union all
  select 4 as id, 'd' as name
  union all
  select 5 as id, 'c' as name
  union all
  select 6 as id, 'aa' as name
  union all
  select 7 as id, 'aa' as name
  union all
  select 8 as id, 'e' as name
  union all
  select 9 as id, 'f' as name
  union all 
  select 10 as id, 'g' as name 
)
SELECT
  max(id) as id,
 concat_ws(collect_list(name,'|')) AS name_
FROM
  (
    SELECT
      id,
      name,
      (id - row_number() OVER (PARTITION BY name ORDER BY id)) AS grp
    FROM
      data_
  ) subquery
GROUP BY
  grp