1、显示百分比形式
-- 保留小数点后四位 * 100,连接%
concat(round(x/y, 4) * 100, '%')
例:
2、取出日期年月
-- 截取字符串
substr(ord_dt, 1, 6)
例:
3、分组后,从每个组内取出各自的TOP N
row_number() OVER (partition by COL1 order by COL2 desc ) rank
说明: 参考自: https://www.jianshu.com/p/9802f3a035e1
partition by:类似hive的建表,分区的意思;
order by:排序,默认是升序,加desc降序;
rank:表示别名
表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)
例:
SELECT *
FROM
(
SELECT ...,
row_number() over (partition by substr(ord_dt, 1, 6) order by COUNT(1) desc ) rank
FROM table_name
WHERE ....
GROUP by ..., substr(ord_dt, 1, 6),...
) c
WHERE c.rank <= 20
4、将20190410这种格式的日期转换为2019-04-10的格式
-- 先将ord_dt转换为Unix时间戳,再将时间戳秒数转换为指定格式的日期
select from_unixtime(unix_timestamp(ord_dt,'yyyymmdd'),'yyyy-mm-dd');
例:
5、根据2019-04-10这种格式的日期统计该日期是2019年的第几周
weekofyear('2019-04-10');
例:
注意:使用时需要注意跨年的时候有异常,比如20181231是周一,结果会输出1,表示为2019年的第一周
补充(慎用):
当每天都有订单的前提下,按周分组统计并且需要每周的日期范围,可以使用下面方法
-- 取出每周日期范围
concat(MIN(ord_dt), "~", MAX(ord_dt)) AS dt,