hive计算世界完全对称日 hive生成序列 hive格式化日期

最新推荐文章于 2024-03-19 12:50:45 发布

xiaofengclouder

最新推荐文章于 2024-03-19 12:50:45 发布

阅读量1.2k

点赞数

分类专栏： hive 小技巧文章标签： hive hadoop sql date 大数据

本文链接：https://blog.csdn.net/sinat_25207217/article/details/121670008

版权

小技巧同时被 2 个专栏收录

18 篇文章 0 订阅

订阅专栏

hive

4 篇文章 0 订阅

订阅专栏

当前日期 2021年12月2日

--使用hive进行格式化
select date_format(current_date(),'yyyyMMdd');  --20211202  即为世界完全对称日

然后百度世界完全对称日
只给出寥寥无几的结果
21世纪对称日

此时回忆起之前曾用sql直接生成了一张日期维表，当时通过posexplode这个函数实现了hive自增序列，再结合其他日期函数可直接生成任意时间跨度的时间维表

posexpode函数演示

select tbl.* from (select 1) tmp lateral view posexplode(split('123456789','')) tbl;
--posexplode会自动映射出 pos和val两个列名  所需参数为一个数组  这里采用split函数实现
+------+------+
| pos  | val  |
+------+------+
| 0    | 1    |
| 1    | 2    |
| 2    | 3    |
| 3    | 4    |
| 4    | 5    |
| 5    | 6    |
| 6    | 7    |
| 7    | 8    |
| 8    | 9    |
| 9    |      |
+------+------+

查询当前至未来10000天的对称日

WITH base
AS (
    SELECT pos
        ,date_format(date_add(CURRENT_DATE(), pos), 'yyyyMMdd') dt
    FROM (
        SELECT 1
        ) t lateral VIEW posexplode(split(space(10000), '')) a
    )
SELECT *
FROM base
WHERE substring(dt, 1, 4) = reverse(substring(dt, 5, 4));
--此处使用space函数生成了长度为10000的字符串，不过如果你想直接输入一个这么长了...
+-------+-----------+
|  pos  |    dt     |
+-------+-----------+
| 0     | 20211202  |
| 3012  | 20300302  |
| 6696  | 20400402  |
+-------+-----------+

查询当前至未来1000,0000天的对称日【格式化为yyyy-MM-dd，只选取日期列合并到一起】

WITH base
AS (
    SELECT pos
        ,date_add(CURRENT_DATE(), pos) dt
    FROM (
        SELECT 1
        ) t lateral VIEW posexplode(split(space(10000000), '')) a
    )
SELECT collect_list(dt)
FROM base
WHERE substring(dt, 1, 4) = reverse(regexp_replace(substring(dt, 6, 5), '-', ''));
--行数过多  使用collect_list函数实现行转列
+----------------------------------------------------+
|                        _c0                         |
+----------------------------------------------------+
| ["2021-12-02","2030-03-02","2040-04-02","2050-05-02","2060-06-02","2070-07-02","2080-08-02","2090-09-02","2101-10-12","2110-01-12","2111-11-12","2120-02-12","2121-12-12","2130-03-12","2140-04-12","2150-05-12","2160-06-12","2170-07-12","2180-08-12","2190-09-12","2201-10-22","2210-01-22","2211-11-22","2220-02-22","2221-12-22","2230-03-22","2240-04-22","2250-05-22","2260-06-22","2270-07-22","2280-08-22","2290-09-22","3001-10-03","3010-01-03","3011-11-03","3020-02-03","3021-12-03","3030-03-03","3040-04-03","3050-05-03","3060-06-03","3070-07-03","3080-08-03","3090-09-03","3101-10-13","3110-01-13","3111-11-13","3120-02-13","3121-12-13","3130-03-13","3140-04-13","3150-05-13","3160-06-13","3170-07-13","3180-08-13","3190-09-13","3201-10-23","3210-01-23","3211-11-23","3220-02-23","3221-12-23","3230-03-23","3240-04-23","3250-05-23","3260-06-23","3270-07-23","3280-08-23","3290-09-23","4001-10-04","4010-01-04","4011-11-04","4020-02-04","4021-12-04","4030-03-04","4040-04-04","4050-05-04","4060-06-04","4070-07-04","4080-08-04","4090-09-04","4101-10-14","4110-01-14","4111-11-14","4120-02-14","4121-12-14","4130-03-14","4140-04-14","4150-05-14","4160-06-14","4170-07-14","4180-08-14","4190-09-14","4201-10-24","4210-01-24","4211-11-24","4220-02-24","4221-12-24","4230-03-24","4240-04-24","4250-05-24","4260-06-24","4270-07-24","4280-08-24","4290-09-24","5001-10-05","5010-01-05","5011-11-05","5020-02-05","5021-12-05","5030-03-05","5040-04-05","5050-05-05","5060-06-05","5070-07-05","5080-08-05","5090-09-05","5101-10-15","5110-01-15","5111-11-15","5120-02-15","5121-12-15","5130-03-15","5140-04-15","5150-05-15","5160-06-15","5170-07-15","5180-08-15","5190-09-15","5201-10-25","5210-01-25","5211-11-25","5220-02-25","5221-12-25","5230-03-25","5240-04-25","5250-05-25","5260-06-25","5270-07-25","5280-08-25","5290-09-25","6001-10-06","6010-01-06","6011-11-06","6020-02-06","6021-12-06","6030-03-06","6040-04-06","6050-05-06","6060-06-06","6070-07-06","6080-08-06","6090-09-06","6101-10-16","6110-01-16","6111-11-16","6120-02-16","6121-12-16","6130-03-16","6140-04-16","6150-05-16","6160-06-16","6170-07-16","6180-08-16","6190-09-16","6201-10-26","6210-01-26","6211-11-26","6220-02-26","6221-12-26","6230-03-26","6240-04-26","6250-05-26","6260-06-26","6270-07-26","6280-08-26","6290-09-26","7001-10-07","7010-01-07","7011-11-07","7020-02-07","7021-12-07","7030-03-07","7040-04-07","7050-05-07","7060-06-07","7070-07-07","7080-08-07","7090-09-07","7101-10-17","7110-01-17","7111-11-17","7120-02-17","7121-12-17","7130-03-17","7140-04-17","7150-05-17","7160-06-17","7170-07-17","7180-08-17","7190-09-17","7201-10-27","7210-01-27","7211-11-27","7220-02-27","7221-12-27","7230-03-27","7240-04-27","7250-05-27","7260-06-27","7270-07-27","7280-08-27","7290-09-27","8001-10-08","8010-01-08","8011-11-08","8020-02-08","8021-12-08","8030-03-08","8040-04-08","8050-05-08","8060-06-08","8070-07-08","8080-08-08","8090-09-08","8101-10-18","8110-01-18","8111-11-18","8120-02-18","8121-12-18","8130-03-18","8140-04-18","8150-05-18","8160-06-18","8170-07-18","8180-08-18","8190-09-18","8201-10-28","8210-01-28","8211-11-28","8220-02-28","8221-12-28","8230-03-28","8240-04-28","8250-05-28","8260-06-28","8270-07-28","8280-08-28","8290-09-28","9001-10-09","9010-01-09","9011-11-09","9020-02-09","9021-12-09","9030-03-09","9040-04-09","9050-05-09","9060-06-09","9070-07-09","9080-08-09","9090-09-09","9101-10-19","9110-01-19","9111-11-19","9120-02-19","9121-12-19","9130-03-19","9140-04-19","9150-05-19","9160-06-19","9170-07-19","9180-08-19","9190-09-19","9201-10-29","9210-01-29","9211-11-29","9220-02-29","9221-12-29","9230-03-29","9240-04-29","9250-05-29","9260-06-29","9270-07-29","9280-08-29","9290-09-29"] |
+----------------------------------------------------+