场景：数据库中id、toapp、topin、toclienttype几个字段都相同，receivetime字段不一样，现需要将receive最小的一行查出，其他行舍去。
select
*
from
(
select
*,
row_number() over(partition by id order by receivetime asc) num
from
xxxxxxxxxxxxxxxxxxxx
where
dt = '-01-14'
and toapp = 'xxxxxx'
and toclienttype = 'xxxxxx'
and msgrectype = '1'
and systemtime is not null
) as t
where
t.num = 1
这里主要的代码就是row_number() OVER (PARTITION BYCOL1ORDER BYCOL2)
这行代码的意思是先对COL1列进行分组，然后按照COL2进行排序，row_number()函数是对分组后的每个组内记录按照COL2排序标号，我们最后取的时候就拿标号为1的一条记录，即达到我的需求。
例子：
empid deptid salary
----------- ----------- ---------------------------------------
1 10 5500.00
2 10 4500.00
3 20 1900.00
4 20 4800.00
5 40 6500.00
6 40 14500.00
7 40 44500.00
8 50 6500.00
9 50 7500.00
row_number() OVER (PARTITION BYdeptidORDER BYsalary)
SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee
结果：
empid deptid salary rank
----------- ----------- --------------------------------------- --------------------
1 10 5500.00 1
2 10 4500.00 2
4 20 4800.00 1
3 20 1900.00 2
7 40 44500.00 1
6 40 14500.00 2
5 40 6500.00 3
9 50 7500.00 1
8 50 6500.00 2
————————————————
版权声明：本文为CSDN博主「dancheren」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：/dancheren/article/details/86481376
问题：发现存在一张表中存在相同的两行数据
得到：仅仅保留一行数据
方法：
原理-我们通过
1 select count (字段1，字段2) from 表1；
2
3 结果 200条数据
4
5 select count (distinct 字段1，字段2) from 表1；
6
7 结果 100条数据
8
9 相当于后者可以实现查出来去重后的数据
10
11 create table 表1_bak as select distinct 字段1，字段2 from 表1; --备份表数据
12
13 delete from 表1;
14
15 insert into 表1 select * from 表1_bak；
Hive中使用Distinct踩到的坑
问题描述：
在使用Hive的过程中，用Distinct对重复数据进行过滤，得出了一个违背认知的结果，百思不得其解。
假设：test表中有100W数据，对test表按照a, b, c, d, e去重。
一、使用Distinct的SQL如下：
SQL1 ：select count(distinct a, b, c, d, e) from test;
得出结果： 2W+。
根据数据特点第一感觉，并不会有那么多重复数据，对自己的distinct使用产生了怀疑，因此用group by校验结果。
二、使用Group by的SQL如下：
SQL2 ：select sum (gcount) from (select count(*) gcountfrom test group bya, b, c, d, e) t
得出结果： 80W+。
这个结果是符合数据特点的；
三、修改SQL1，去掉一个字段；
SQL3：select count(distinct b, c, d, e) from test;
得出结果：90W+。
四、对比SQL1和 SQL3
按照4个字段distinct 理论上一定比 5个字段distinct 结果少， 测试结果缺恰恰相反；
原因就是因为a列中包含null， 按我的认知以为所有的null值会被归结为同一个，可实际上hive并不会；
所以distinct的列中如果含有null值，会导致结果不准，需要将null值替换为一个统一的值。
修改如下：
select count(distinct nvl(a, 0), b, c, d, e) from test;
如上，问题解决！
————————————————
版权声明：本文为CSDN博主「UncleMing5371」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：/UncleMing5371/article/details/85236709
hive中的distinct用法：
HIVESQL中ROW_NUMBER() OVER语法以及示例
ROW_NUMBER() OVER函数的基本用法
语法：ROW_NUMBER() OVER(PARTITION BY COLUMNORDER BY COLUMN)
详解：
row_number() OVER (PARTITION BY COL1 ORDERBY COL2)表示根据COL1分组，在分组内部根据COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号(该编号在组内是连续并且唯一的)。
场景描述：
在Hive中employee表包括empid、depid、salary三个字段，根据部门分组，显示每个部门的工资等级。
1、原表查看：在Hive中employee表及其内容如下所示：
2、执行SQL。
SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee
3、查看结果。
————————————————
版权声明：本文为CSDN博主「汀桦坞」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：/wiborgite/article/details/80521593
我的使用：
(SELECT * FROM tm_data_room_${this_ds}_${this_ts}_per_hour_tmp1) as a
left join
(
SELECT DISTINCT(k.setid),k.ds,k.ts,k.game_mode,k.create_clubid as clubid,k.leagueid,k.is_satellite,k.is_entity,k.ensure_chips,k.gameset_end_time from
(
select * from
(select *,Row_Number() OVER (partition by setid ORDER BY gameset_end_time desc) rank from gameset_info_log_flow
where ds = ${this_ds} and ts = ${this_ts} and room_mode = 3 and gameset_status=100 and gameset_start_time != 0 and gameset_end_time !=0
) as t
where t.rank = 1
) as k
--gameset_info_log_flow
--where ds = ${this_ds} and ts = ${this_ts} and room_mode = 3 and gameset_status=100 and gameset_start_time != 0 and gameset_end_time !=0
) as b
on a.setid = b.setid
