MaxCompute SQL使用小技巧之时间日期处理

本文涉及的产品
云原生大数据计算服务MaxCompute,500CU*H 100GB 3个月
简介: 业务需求分析中经常会对时间日期进行处理,MaxComputer中也有很多关于日期的处理函数,文章就是对这些内置函数相互嵌套使用达到分析要求的分析

我们分析需求时经常会遇到如:最近一周、最近一个月、月初、月末、本季度...等等的时间修饰词,这类修饰词就构成了派生指标。使用Maxcomputer的童鞋都知道,其内置函数和Hive还是有些区别的,接下来我们就看看如何使用Maxcomputer内置日期函数。

  • 时间戳与日期的相互转换
select from_unixtime(1629785626);--2021-08-24 14:13:46select unix_timestamp(datetime'2021-08-24 14:13:46');--1629785626
  • yyyymmdd与yyyy-mm-dd之间相互转换
select to_char(to_date('20210824','yyyymmdd'),'yyyy-mm-dd');--2021-08-24select to_char(to_date('2021-08-24','yyyy-mm-dd'),'yyyymmdd');--20210824
  • 获取年份
selectyear('2021-08-24 14:13:46');--2021
  • 获取月份
select datepart(datetime'2021-08-24 14:13:46','mm');--8select month('2021-08-24 14:13:46');--8
  • 获取日
select datepart(datetime'2021-08-24 14:13:46','dd');--24select day('2021-08-24 14:13:46');--24
  • 获取小时
select datepart(datetime'2021-08-24 14:13:46','hh');--14select hour('2021-08-24 14:13:46');--14
  • 获取分钟
select datepart(datetime'2021-08-24 14:13:46','mi');--13select minute('2021-08-24 14:13:46');--13
  • 获取秒
select datepart(datetime'2021-08-24 14:13:46','ss');--46select second('2021-08-24 14:13:46');--46
  • 本周第几天
select weekday(to_date('2021-08-24','yyyy-mm-dd'));--1 周一返回的是0,周日为6
  • 本月第几天
select dayofmonth('2021-08-24');--24
  • 本年第几周
select weekofyear(to_date('2021-08-24','yyyy-mm-dd'));--34
  • 本年第几天
select datediff(to_date('2021-08-24','yyyy-mm-dd'),datetrunc(to_date('2021-08-24','yyyy-mm-dd'),'yyyy'));--235
  • 本年第几季度
select quarter('2021-08-24');--3
  • 本周第一天
select date_add(next_day('2021-08-24','MO'),-7);--2021-08-23
  • 本周最后一天
select date_add(next_day('2021-08-24','MO'),-1);--2021-08-29
  • 最近一周
select date_add('2021-08-24',-7);--2021-08-17
  • 已知日期返回周几,2018-01-01刚好是周一
select case pmod(datediff('2021-08-24','2018-01-01'),7)            when 0 then '周一'            when 1 then '周二'            when 2 then '周三'            when 3 then '周四'            when 4 then '周五'            when 5 then '周六'            when 6 then '周日'        end ;--周二
  • 上周一
select date_add(next_day('2021-08-24','MO'),-14);--2021-08-16
  • 上周末
select date_add(next_day('2021-08-24','MO'),-8);--2021-08-22
  • 本月第一天
select datetrunc(to_date('2021-08-24','yyyy-mm-dd'),'mm');--2021-08-01 00:00:00select date_sub('2021-08-24',dayofmonth('2021-08-24')-1);--2021-08-01
  • 本月最后一天
select lastday(to_date('2021-08-24','yyyy-mm-dd'));--2021-08-31select last_day('2021-08-24');--2021-08-31
  • 上个月月末
select dateadd(to_date(last_day('2021-08-24'),'yyyy-mm-dd'),-1,'mm');--2021-07-31 00:00:00
  • 上个月初
select add_months(date_sub('2021-08-24',dayofmonth('2021-08-24')-1),-1);--2021-07-01
  • 上个月同期
select add_months(to_date('2021-08-24','yyyy-mm-dd'),-1);--2021-07-24
  • 本季度第一天
select dateadd(dateadd(datetrunc(to_date('2021-08-24','yyyy-mm-dd'),'year'),quarter(to_date('2021-08-24','yyyy-mm-dd'))*3,'month'),-1,'mm');--2021-09-01 00:00:00
  • 本季度最后一天
select dateadd(dateadd(datetrunc(to_date('2021-08-24','yyyy-mm-dd'),'year'),quarter(to_date('2021-08-24','yyyy-mm-dd'))*3,'month'),-1,'dd');--2021-09-30 00:00:00
  • 上个季度第一天
select dateadd(dateadd(datetrunc(to_date('2021-08-24','yyyy-mm-dd'),'year'),(quarter(to_date('2021-08-24','yyyy-mm-dd'))-1)*3,'month'),-1,'mm');--2021-06-01 00:00:00
  • 上个季度末一天
select dateadd(dateadd(datetrunc(to_date('2021-08-24','yyyy-mm-dd'),'year'),(quarter(to_date('2021-08-24','yyyy-mm-dd'))-1)*3,'month'),-1,'dd');--2021-06-30 00:00:00
  • 本年第一天
select datetrunc(to_date('2021-08-24','yyyy-mm-dd'),'year');--2021-01-01 00:00:00
  • 本年最后一天
select date_add(add_months(datetrunc(to_date('2021-08-24','yyyy-mm-dd'),'year'),12),-1);--2021-12-31
  • 去年同期
select add_months(to_date('2021-08-24','yyyy-mm-dd'),-12);--2020-08-24

关于日期之间的转换有很多中方式,不限于以上方式,希望能帮助到小伙伴

拜了个拜

相关实践学习
基于MaxCompute的热门话题分析
本实验围绕社交用户发布的文章做了详尽的分析,通过分析能得到用户群体年龄分布,性别分布,地理位置分布,以及热门话题的热度。
SaaS 模式云数据仓库必修课
本课程由阿里云开发者社区和阿里云大数据团队共同出品,是SaaS模式云原生数据仓库领导者MaxCompute核心课程。本课程由阿里云资深产品和技术专家们从概念到方法,从场景到实践,体系化的将阿里巴巴飞天大数据平台10多年的经过验证的方法与实践深入浅出的讲给开发者们。帮助大数据开发者快速了解并掌握SaaS模式的云原生的数据仓库,助力开发者学习了解先进的技术栈,并能在实际业务中敏捷的进行大数据分析,赋能企业业务。 通过本课程可以了解SaaS模式云原生数据仓库领导者MaxCompute核心功能及典型适用场景,可应用MaxCompute实现数仓搭建,快速进行大数据分析。适合大数据工程师、大数据分析师 大量数据需要处理、存储和管理,需要搭建数据仓库?学它! 没有足够人员和经验来运维大数据平台,不想自建IDC买机器,需要免运维的大数据平台?会SQL就等于会大数据?学它! 想知道大数据用得对不对,想用更少的钱得到持续演进的数仓能力?获得极致弹性的计算资源和更好的性能,以及持续保护数据安全的生产环境?学它! 想要获得灵活的分析能力,快速洞察数据规律特征?想要兼得数据湖的灵活性与数据仓库的成长性?学它! 出品人:阿里云大数据产品及研发团队专家 产品 MaxCompute 官网 https://www.aliyun.com/product/odps 
目录
打赏
0
0
0
0
106
分享
相关文章
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL 数据库 SQL 语句调优方法详解(2-1)
本文深入介绍 MySQL 数据库 SQL 语句调优方法。涵盖分析查询执行计划,如使用 EXPLAIN 命令及理解关键指标;优化查询语句结构,包括避免子查询、减少函数使用、合理用索引列及避免 “OR”。还介绍了索引类型知识,如 B 树索引、哈希索引等。结合与 MySQL 数据库课程设计相关文章,强调 SQL 语句调优重要性。为提升数据库性能提供实用方法,适合数据库管理员和开发人员。
大数据新视界--大数据大厂之MySQL 数据库课程设计:MySQL 数据库 SQL 语句调优的进阶策略与实际案例(2-2)
本文延续前篇,深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用,介绍多种索引类型及避免索引失效等;调整数据库参数,如缓冲池、连接数和日志参数;还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章,强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导,确保数据库高效运行。
玩转大数据:从零开始掌握SQL查询基础
玩转大数据:从零开始掌握SQL查询基础
209 35
为什么大数据平台会回归SQL
在大数据领域,尽管非结构化数据占据了大数据平台80%以上的存储空间,结构化数据分析依然是核心任务。SQL因其广泛的应用基础和易于上手的特点成为大数据处理的主要语言,各大厂商纷纷支持SQL以提高市场竞争力。然而,SQL在处理复杂计算时表现出的性能和开发效率低下问题日益凸显,如难以充分利用现代硬件能力、复杂SQL优化困难等。为了解决这些问题,出现了像SPL这样的开源计算引擎,它通过提供更高效的开发体验和计算性能,以及对多种数据源的支持,为大数据处理带来了新的解决方案。
比 SQL 快出数量级的大数据计算技术
SQL 是大数据计算中最常用的工具,但在实际应用中,SQL 经常跑得很慢,浪费大量硬件资源。例如,某银行的反洗钱计算在 11 节点的 Vertica 集群上跑了 1.5 小时,而用 SPL 重写后,单机只需 26 秒。类似地,电商漏斗运算和时空碰撞任务在使用 SPL 后,性能也大幅提升。这是因为 SQL 无法写出低复杂度的算法,而 SPL 提供了更强大的数据类型和基础运算,能够实现高效计算。
大数据-160 Apache Kylin 构建Cube 按照日期构建Cube 详细记录
大数据-160 Apache Kylin 构建Cube 按照日期构建Cube 详细记录
147 2
sql server日期时间函数
sql server日期时间函数
131 2
ODPS SQL ——列转行、行转列这回让我玩明白了!
本文详细介绍了在MaxCompute中如何使用TRANS_ARRAY和LATERAL VIEW EXPLODE函数来实现列转行的功能。
|
9月前
|
大数据-161 Apache Kylin 构建Cube 按照日期、区域、产品、渠道 与 Cube 优化
大数据-161 Apache Kylin 构建Cube 按照日期、区域、产品、渠道 与 Cube 优化
129 0

热门文章

最新文章

AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等