去重

dense_ran()

场景:两个人并列第1,后续的值为2
|函数 |场景 |
| —————— | —————— |
|rank | 两个人并列第1,后继的值为3 |
|dense_ran | 两个人并列第1,后续的值为2 |
|row_number | 不存在并列,序号不重 |

row_number

  1. select *
  2. from (
  3. select *,row_number() over (partition by id order by occur_timestamp desc) as order_num
  4. from
  5. bigdata.xxx where dt='2019-01-22'
  6. ) dd2 where dd2.order_num=1;

row_number() over (partition by id order by occur_timestamp desc) as order_num

order_num=1

  1. SELECT tel, link_name, certificate_no, certificate_type, modify_time
  2. FROM order_info
  3. WHERE deleted = 'F'
  4. AND pay_status = 'payed'
  5. AND create_time >= to_date('2017-04-23', 'yyyy-MM-dd')
  6. AND create_time < to_date('2017-04-24', 'yyyy-MM-dd')
  7. AND row_number() over(PARTITION BY tel ORDER BY tel DESC) = 1

https://www.cnblogs.com/wujin/p/6051768.html

文档更新时间: 2019-06-26 18:53