一个sql生成hive日期维度表

简介: 一个sql生成hive日期维度表

前言


在进行日期处理时,有时候会很麻烦,于是小编开发了一张日期维表,供大家参考。


正文


1、日期维度表


num 字段名 字段中文名

描述

数据类型

1

date 日期 日期 yyyMMdd格式

bigint

2

week 星期,数字型 星期,数字型 0-6 bigint

3

week_cn 星期中文名 星期中文名 星期一…… string

4

year_weeks 一年中的第几周
一年中的第几周 1 2 3……
bigint

5

mon_dt 本周周一日期 本周周一日期 bigint

6

sun_dt sun_dt
本周周日日期
bigint

7

month

年月 年月,yyyyMM格式 bigint

8

month_short
月份简写
月份简写,MM格式1~12 bigint

9

month_cn


月份中文名 一月…… string

10


quarter
季度 季度,yyyyQ1\2\3\4 string

11

quarter_short 季度 数字型 季度 数字型 1-4 bigint


2、生成语句


set hive.execution.engine=tez;

with dates as (

select date_add("2010-01-01", a.pos) as d

from (select posexplode(split(repeat("o", datediff("2030-12-31", "2010-01-01")), "o"))) a

)

insert overwrite table dim.dim_date

select

   d

 , date_format(d, 'yyyyMMdd000000') as to_pt            -- 指定分区格式

 , date_format(d, 'yyyyMMdd')       as date_yyyymmdd

 , trunc(d,'MM')                    as month_first_day

 , last_day(d)                      as month_last_day

 , date_format(last_day(d),'yyyyMMdd000000')   as month_last_pt

 , date_format(d, 'yyyyMM')  as month_yyyymm

 , date_format(d, 'yyyy-MM') as month_yyyy_mm

 , month(d) as month

 , date_format(d, 'u') as week

 , date_format(d, 'E') as week_long  

 , weekofyear(d) as week_of_year

 , year(d) as year

 , floor(substr(d,6,2)/3.1)*3+1 as quarter

 -- , concat_group('"',date_format(d, 'yyyyMM'),'"') as date_yyyymmdd_list   -- 低版本hive group_concat 不可用

from dates


3、用例


取月末:where date_pk = month_last_day;

取周末:where week_int in (6,7);

取每月最后一天pt+ 当月昨天pt:where pt IN ( SELECT max(to_pt) FROM dim.dim_date where to_pt <= '${-1d_pt}' group by month_yyyymm  );

其它用法......


相关文章
|
1天前
|
SQL 分布式计算 Hadoop
Hadoop-12-Hive 基本介绍 下载安装配置 MariaDB安装 3台云服务Hadoop集群 架构图 对比SQL HQL
Hadoop-12-Hive 基本介绍 下载安装配置 MariaDB安装 3台云服务Hadoop集群 架构图 对比SQL HQL
10 3
|
3天前
|
SQL 开发框架 .NET
sql server日期时间函数
sql server日期时间函数
13 2
|
23小时前
|
SQL 分布式计算 关系型数据库
Hadoop-24 Sqoop迁移 MySQL到Hive 与 Hive到MySQL SQL生成数据 HDFS集群 Sqoop import jdbc ETL MapReduce
Hadoop-24 Sqoop迁移 MySQL到Hive 与 Hive到MySQL SQL生成数据 HDFS集群 Sqoop import jdbc ETL MapReduce
7 0
|
2月前
|
SQL 存储 分布式计算
插入Hive表数据SQL
【8月更文挑战第10天】
|
2月前
|
SQL 物联网 数据处理
"颠覆传统,Hive SQL与Flink激情碰撞!解锁流批一体数据处理新纪元,让数据决策力瞬间爆表,你准备好了吗?"
【8月更文挑战第9天】数据时代,实时性和准确性至关重要。传统上,批处理与流处理各司其职,但Apache Flink打破了这一界限,尤其Flink与Hive SQL的结合,开创了流批一体的数据处理新时代。这不仅简化了数据处理流程,还极大提升了效率和灵活性。例如,通过Flink SQL,可以轻松实现流数据与批数据的融合分析,无需在两者间切换。这种融合不仅降低了技术门槛,还为企业提供了更强大的数据支持,无论是在金融、电商还是物联网领域,都将发挥巨大作用。
45 6
|
2月前
|
Java 应用服务中间件 Maven
从零到英雄:一步步构建你的首个 JSF 应用程序,揭开 JavaServer Faces 的神秘面纱
【8月更文挑战第31天】JavaServer Faces (JSF) 是一种强大的 Java EE 标准,用于构建企业级 Web 应用。它提供了丰富的组件库和声明式页面描述语言 Facelets,便于开发者快速开发功能完善且易于维护的 Web 应用。本文将指导你从零开始构建一个简单的 JSF 应用,包括环境搭建、依赖配置、Managed Bean 编写及 Facelets 页面设计。
70 0
|
2月前
|
SQL 关系型数据库 MySQL
【超全整理】SQL日期与时间函数大汇总会:MySQL与SQL Server双轨对比教学,助你轻松搞定时间数据处理难题!
【8月更文挑战第31天】本文介绍了在不同SQL数据库系统(如MySQL、SQL Server、Oracle)中常用的日期与时间函数,包括DATE、NOW()、EXTRACT()、DATE_ADD()、TIMESTAMPDIFF()及日期格式化等,并提供了具体示例。通过对比这些函数在各系统中的使用方法,帮助开发者更高效地处理日期时间数据,满足多种应用场景需求。
98 0
|
3月前
|
SQL 监控 关系型数据库
PolarDB产品使用问题之SQL防火墙怎么拦截没有指定WHERE条件的特定表的SQL语
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
3月前
|
SQL 分布式计算 DataWorks
MaxCompute操作报错合集之使用sql查询一个表的分区数据时遇到报错,该如何解决
MaxCompute是阿里云提供的大规模离线数据处理服务,用于大数据分析、挖掘和报表生成等场景。在使用MaxCompute进行数据处理时,可能会遇到各种操作报错。以下是一些常见的MaxCompute操作报错及其可能的原因与解决措施的合集。