表如下:
a b
1 10
2 11
3 11
4 11
5 12
6 12
7 13
拿出b列连续重复的第一个行。输出如下:
1 10
2 11
5 12
7 13
答案:
select
t1.a,
t1.b
from ( select 1 a,
10 b
union all select 2 a,
11 b
union all select 3 a,
11 b
union all select 4 a,
11 b
union all select 5 a,
12 b
union all select 6 a,
12 b
union all select 7 a,
13 b) t1
left join
(select 1 a,
10 b
union all select 2 a,
11 b
union all select 3 a,
11 b
union all select 4 a,
11 b
union all select 5 a,
12 b
union all select 6 a,
12 b
union all select 7 a,
13 b) t2
on t1.a = t2.a + 1
where case when t1.b = t2.b then null else t1.b end is not null
;
表字段:
live_id 直播id,
userid 用户id,
date_stamp 时间戳,
entry_type 登陆状态,登陆(enter)和登出(out)
答案:
select live_id,
date_stamp,
cnt
from (
select live_id,
date_stamp,
cnt,
rank() over(order by cnt desc) rn
from (
select live_id,
date_stamp,
sum(flag) over (order by date_stamp) as cnt
from (
select live_id,
userid,
date_stamp as date_stamp,
case when entry_type = 'enter' then 1
when entry_type = 'out' then -1
else 0
end flag
from (
select 1 live_id,
'A' userid,
'20200801 10:00:00' date_stamp,
'enter' entry_type
union all select 1 live_id,
'B' userid,
'20200801 10:01:00' date_stamp,
'enter' entry_type
union all select 1 live_id,
'A' userid,
'20200801 10:01:01' date_stamp,
'out' entry_type
union all select 1 live_id,
'C' userid,
'20200801 10:02:03' date_stamp,
'enter' entry_type
)
)
group by live_id,
date_stamp,
flag
) t1
) t2
where rn = 1
表如下:
a b
1 10
2 11
3 11
4 11
5 12
6 12
7 13
执行sql:
select a,
b,
row_number() over(order by b) row_number,
rank() over(order by b) rank,
dense_rank() over(order by b) dense_rank
from (
select 1 a,
10 b
union all select 2 a,
11 b
union all select 3 a,
11 b
union all select 4 a,
11 b
union all select 5 a,
12 b
union all select 6 a,
12 b
union all select 7 a,
13 b
) t
结果:
a b row_number rank dense_rank
1 10 1 1 1
2 11 2 2 2
3 11 3 2 2
4 11 4 2 2
5 12 5 5 3
6 12 6 5 3
7 13 7 7 4
总结:
row_number:不管排名是否有相同的,都按照顺序1,2,3...
rank:排名相同的名次一样,同一排名有几个,后面排名就会跳过几次
dense_rank:排名相同的名次一样,且后面名次不跳跃
array:array中的数据为相同类型,例如,假如array A中元素['a','b','c'],则A[0]的值为'a'。
map(K-V对):访问指定域可以通过["指定域名称"]进行,例如,一个Map M包含了一个{"name":"b"}的kv对,gid的值可以通过M['name']来获取。
struct:struct内部的数据可以通过DOT(.)来存取,例如,表中一列c的类型为STRUCT{a INT; b INT},我们可以通过c.a来访问域a。
json:通过get_json_object获取,例如,tag字段{"data":"test"},可以通过get_json_object(tag, '$.data')获取。