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

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

拜了个拜

相关实践学习
基于Hologres轻松玩转一站式实时仓库
本场景介绍如何利用阿里云MaxCompute、实时计算Flink和交互式分析服务Hologres开发离线、实时数据融合分析的数据大屏应用。
基于MaxCompute的热门话题分析
Apsara Clouder大数据专项技能认证配套课程:基于MaxCompute的热门话题分析
目录
相关文章
|
30天前
|
SQL 分布式计算 大数据
SparkSQL 入门指南:小白也能懂的大数据 SQL 处理神器
在大数据处理的领域,SparkSQL 是一种非常强大的工具,它可以让开发人员以 SQL 的方式处理和查询大规模数据集。SparkSQL 集成了 SQL 查询引擎和 Spark 的分布式计算引擎,使得我们可以在分布式环境下执行 SQL 查询,并能利用 Spark 的强大计算能力进行数据分析。
|
5月前
|
SQL 关系型数据库 MySQL
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL 数据库 SQL 语句调优方法详解(2-1)
本文深入介绍 MySQL 数据库 SQL 语句调优方法。涵盖分析查询执行计划,如使用 EXPLAIN 命令及理解关键指标;优化查询语句结构,包括避免子查询、减少函数使用、合理用索引列及避免 “OR”。还介绍了索引类型知识,如 B 树索引、哈希索引等。结合与 MySQL 数据库课程设计相关文章,强调 SQL 语句调优重要性。为提升数据库性能提供实用方法,适合数据库管理员和开发人员。
|
5月前
|
关系型数据库 MySQL 大数据
大数据新视界--大数据大厂之MySQL 数据库课程设计:MySQL 数据库 SQL 语句调优的进阶策略与实际案例(2-2)
本文延续前篇,深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用,介绍多种索引类型及避免索引失效等;调整数据库参数,如缓冲池、连接数和日志参数;还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章,强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导,确保数据库高效运行。
|
6月前
|
SQL 大数据 数据挖掘
玩转大数据:从零开始掌握SQL查询基础
玩转大数据:从零开始掌握SQL查询基础
238 35
|
10月前
|
SQL 算法 大数据
为什么大数据平台会回归SQL
在大数据领域,尽管非结构化数据占据了大数据平台80%以上的存储空间,结构化数据分析依然是核心任务。SQL因其广泛的应用基础和易于上手的特点成为大数据处理的主要语言,各大厂商纷纷支持SQL以提高市场竞争力。然而,SQL在处理复杂计算时表现出的性能和开发效率低下问题日益凸显,如难以充分利用现代硬件能力、复杂SQL优化困难等。为了解决这些问题,出现了像SPL这样的开源计算引擎,它通过提供更高效的开发体验和计算性能,以及对多种数据源的支持,为大数据处理带来了新的解决方案。
|
10月前
|
SQL 存储 算法
比 SQL 快出数量级的大数据计算技术
SQL 是大数据计算中最常用的工具,但在实际应用中,SQL 经常跑得很慢,浪费大量硬件资源。例如,某银行的反洗钱计算在 11 节点的 Vertica 集群上跑了 1.5 小时,而用 SPL 重写后,单机只需 26 秒。类似地,电商漏斗运算和时空碰撞任务在使用 SPL 后,性能也大幅提升。这是因为 SQL 无法写出低复杂度的算法,而 SPL 提供了更强大的数据类型和基础运算,能够实现高效计算。
|
11月前
|
SQL 分布式计算 大数据
大数据-160 Apache Kylin 构建Cube 按照日期构建Cube 详细记录
大数据-160 Apache Kylin 构建Cube 按照日期构建Cube 详细记录
165 2
|
11月前
|
SQL 开发框架 .NET
sql server日期时间函数
sql server日期时间函数
145 2
|
12月前
|
SQL JSON 分布式计算
ODPS SQL ——列转行、行转列这回让我玩明白了!
本文详细介绍了在MaxCompute中如何使用TRANS_ARRAY和LATERAL VIEW EXPLODE函数来实现列转行的功能。
|
11月前
|
SQL 关系型数据库 MySQL
SQL日期函数
SQL日期函数
163 0

热门文章

最新文章