【SQL开发实战技巧】系列(十七):数据仓库中时间类型操作(初级)确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数

简介: 如何确定两个日期之间的工作日有多少天、计算—年中每周内各日期出现次数、确定当前记录和下一条记录之间相差的天数【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作,毕竟SQL开发在数据分析场景非常重要且基础,面试也会经常问SQL开发和调优经验,相信当我写完这一系列文章,也能再有所收获,未来面对SQL面试也能游刃有余~。本章节的三个需求:确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数有些许难度,不过建议还是学会比较好。

前言

本篇文章讲解的主要内容是:如何确定两个日期之间的工作日有多少天、计算—年中每周内各日期出现次数、确定当前记录和下一条记录之间相差的天数
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作,毕竟SQL开发在数据分析场景非常重要且基础,面试也会经常问SQL开发和调优经验,相信当我写完这一系列文章,也能再有所收获,未来面对SQL面试也能游刃有余~。


一、确定两个日期之间的工作天数

现在有个需求:返回员工BLAKE与JONES聘用日期之间的工作天数。
先看一下初始数据:

SQL> select ename,hiredate from emp where ename in ('BLAKE','JONES');

ENAME      HIREDATE
---------- -----------
BLAKE      1981-5-1
JONES      1981-4-2

接下来一步步分析这个需求怎么做!
第一步,先初始化个600条数据的临时表T,具体啥用待会给大家说

with t as (
select level as id from dual connect by level<=600
)

第二步,通过max,mingroup by将上面初始数据转为一行,这个结果做第二个临时表T1:

SQL> with t as
  2   (select level as id from dual connect by level <= 600),
  3  t1 as
  4   (select min(hiredate) as min_hd, max(hiredate) as max_hd
  5      from emp
  6     where ename in ('BLAKE', 'JONES'))
  7  select * from t1;

MIN_HD      MAX_HD
----------- -----------
1981-4-2    1981-5-1

第三步,枚举出来两个日期之间的间隔天数,不过日期相减需要+1,比如1~2天是2天,所以计算公式应该是(2-1)+1

SQL> with t as
  2   (select level as id from dual connect by level <= 600),
  3  t1 as
  4   (select min(hiredate) as min_hd, max(hiredate) as max_hd
  5      from emp
  6     where ename in ('BLAKE', 'JONES'))
  7  select (max_hd-min_hd)+1 as 天数 from t1;

        天数
----------
        30

第四步,将T表与T1表做个笛卡尔积,枚举出来这30天的所有日期。

SQL> set pagesize 200;
SQL> 
SQL> with t as
  2   (select level as id from dual connect by level <= 600),
  3  t1 as
  4   (select min(hiredate) as min_hd, max(hiredate) as max_hd
  5      from emp
  6     where ename in ('BLAKE', 'JONES'))
  7  select min_hd + (t.id - 1) as 日期
  8    from t, t1
  9   where t.id <= ((max_hd - min_hd) + 1);

日期
-----------
1981-4-2
1981-4-3
1981-4-4
1981-4-5
1981-4-6
1981-4-7
1981-4-8
1981-4-9
1981-4-10
1981-4-11
1981-4-12
1981-4-13
1981-4-14
1981-4-15
1981-4-16
1981-4-17
1981-4-18
1981-4-19
1981-4-20
1981-4-21
1981-4-22
1981-4-23
1981-4-24
1981-4-25
1981-4-26
1981-4-27
1981-4-28
1981-4-29
1981-4-30
1981-5-1

30 rows selected

第五步,根据这些日期得到对应的工作日信息

SQL> 
SQL> with t as
  2   (select level as id from dual connect by level <= 600),
  3  t1 as
  4   (select min(hiredate) as min_hd, max(hiredate) as max_hd
  5      from emp
  6     where ename in ('BLAKE', 'JONES')),
  7  t2 as
  8   (select min_hd + (t.id - 1) as 日期
  9      from t, t1
 10     where t.id <= ((max_hd - min_hd) + 1))
 11  select 日期, to_char(日期, 'DY', 'NLS_DATE_LANGUAGE=American') as dy
 12    from t2;

日期        DY
----------- ---------------------------------------------------------------------------
1981-4-2    THU
1981-4-3    FRI
1981-4-4    SAT
1981-4-5    SUN
1981-4-6    MON
1981-4-7    TUE
1981-4-8    WED
1981-4-9    THU
1981-4-10   FRI
1981-4-11   SAT
1981-4-12   SUN
1981-4-13   MON
1981-4-14   TUE
1981-4-15   WED
1981-4-16   THU
1981-4-17   FRI
1981-4-18   SAT
1981-4-19   SUN
1981-4-20   MON
1981-4-21   TUE
1981-4-22   WED
1981-4-23   THU
1981-4-24   FRI
1981-4-25   SAT
1981-4-26   SUN
1981-4-27   MON
1981-4-28   TUE
1981-4-29   WED
1981-4-30   THU
1981-5-1    FRI

30 rows selected

第六步,过滤,把得到的结果汇总就是工作天数。

SQL> with t as
  2   (select level as id from dual connect by level <= 600),
  3  t1 as
  4   (select min(hiredate) as min_hd, max(hiredate) as max_hd
  5      from emp
  6     where ename in ('BLAKE', 'JONES')),
  7  t2 as
  8   (select min_hd + (t.id - 1) as 日期
  9      from t, t1
 10     where t.id <= ((max_hd - min_hd) + 1)),
 11  t3 as
 12   (select 日期, to_char(日期, 'DY', 'NLS_DATE_LANGUAGE=American') as dy
 13      from t2)
 14  select count(*) from t3 where dy not in ('SAT', 'SUN');

  COUNT(*)
----------
        22

二、计算—年中周内各日期的次数

比如,计算一年内有多少天是星期一,多少天是星期二等,这个问题需要以下几步。

  1. 取得当前年度信息。
  2. 计算一年有多少天。
  3. 生成日期列表。
  4. 转换为对应的星期标识。
  5. 汇总。

那么接下来看怎么做!

SQL> with t as
  2   (select to_date('2023-01-01', 'yyyy-mm-dd') as 年初 from dual),
  3  t1 as
  4   (select 年初, add_months(年初, 12) as 下年初 from t),
  5  t2 as
  6   (select 年初, 下年初, 下年初 - 年初 as 天数 from t1),
  7  t3 as/*生成列表*/
  8   (select 年初 + (level - 1) as 日期 from t2 connect by level <= 天数),
  9  t4 as/*对数据进行转换*/
 10   (select 日期, to_char(日期, 'DY') as 星期 from t3)
 11  select 星期, count(*) as 天数 from t4 group by 星期;

星期                                                                                天数
--------------------------------------------------------------------------- ----------
星期二                                                                              52
星期六                                                                              52
星期日                                                                              53
星期三                                                                              52
星期四                                                                              52
星期五                                                                              52
星期一                                                                              52

7 rows selected

三、确定当前记录和下一条记录之间相差的天数

首先需要把下一条记录的雇佣日期作为当前行,这需要用到lead()over()分析函数。

SQL> select deptno,
  2         ename,
  3         hiredate,
  4         lead(hiredate) over(order by hiredate) next_hd
  5    from emp
  6   where deptno = 10;

DEPTNO ENAME      HIREDATE    NEXT_HD
------ ---------- ----------- -----------
    10 CLARK      1981-6-9    1981-11-17
    10 KING       1981-11-17  1982-1-23
    10 MILLER     1982-1-23   

当数据提取到同一行后,再计算就比较简单:

SQL> with t as (
  2  select deptno,
  3         ename,
  4         hiredate,
  5         lead(hiredate) over(order by hiredate) next_hd
  6    from emp
  7   where deptno = 10)
  8  select ename,hiredate,next_hd-hiredate diff
  9  from t;

ENAME      HIREDATE          DIFF
---------- ----------- ----------
CLARK      1981-6-9           161
KING       1981-11-17          67
MILLER     1982-1-23   

和lead对应的就是lag函数,如果读者能记住两个函数的区别当然比较好,如果记不住,可直接实验。

SQL> 
SQL> with t as (
  2  select deptno,
  3         ename,
  4         hiredate,
  5         lag(hiredate) over(order by hiredate) lag_hd,
  6         lead(hiredate) over(order by hiredate) lead_hd
  7    from emp
  8   where deptno = 10)
  9  select * from t;

DEPTNO ENAME      HIREDATE    LAG_HD      LEAD_HD
------ ---------- ----------- ----------- -----------
    10 CLARK      1981-6-9                1981-11-17
    10 KING       1981-11-17  1981-6-9    1982-1-23
    10 MILLER     1982-1-23   1981-11-17  

总结

本章节的三个需求:确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数
有些许难度,不过建议还是学会比较好。

相关文章
|
4天前
|
SQL 关系型数据库 MySQL
sql注入原理与实战(三)数据库操作
sql注入原理与实战(三)数据库操作
sql注入原理与实战(三)数据库操作
|
18天前
|
SQL 存储 并行计算
Lindorm Ganos 一条 SQL 计算轨迹
Lindorm Ganos 针对轨迹距离计算场景提供了内置函数 ST_Length_Rows,结合原生时空二级索引和时空聚合计算下推技术,能够高效过滤数据并并行执行运算任务。该方案通过主键索引和时空索引快速过滤数据,并利用多Region并行计算轨迹点距离,适用于车联网等场景。具体步骤包括根据车辆识别代码和时间戳过滤数据、范围过滤轨迹点以及并行计算距离。使用限制包括只支持点类型列聚合运算及表中轨迹点需按顺序排列等。测试结果显示,Lindorm Ganos 在不同数据量下均能实现秒级响应。
18 3
|
2天前
|
SQL 数据处理 数据库
SQL语句优化与查询结果优化:提升数据库性能的实战技巧
在数据库管理和应用中,SQL语句的编写和查询结果的优化是提升数据库性能的关键环节
|
2天前
|
SQL 监控 关系型数据库
SQL语句性能分析:实战技巧与详细方法
在数据库管理中,分析SQL语句的性能是优化数据库查询、提升系统响应速度的重要步骤
|
4天前
|
SQL 关系型数据库 Serverless
sql注入原理与实战(四)数据表操作
sql注入原理与实战(四)数据表操作
|
4天前
|
SQL 存储 Java
sql注入原理与实战(二)数据库原理
sql注入原理与实战(二)数据库原理
|
4天前
|
SQL 前端开发 安全
sql注入原理与实战(一)
sql注入原理与实战(一)
|
2月前
|
存储 缓存 Cloud Native
MPP架构数据仓库使用问题之ADB PG云原生版本的扩缩容性能怎么样
MPP架构数据仓库使用问题之ADB PG云原生版本的扩缩容性能怎么样
MPP架构数据仓库使用问题之ADB PG云原生版本的扩缩容性能怎么样
|
3月前
|
SQL Cloud Native 关系型数据库
云原生数据仓库使用问题之分组优化如何实现
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
|
3月前
|
Cloud Native 关系型数据库 OLAP
云原生数据仓库操作报错合集之遇到“table does not exist”错误,该怎么办
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。

热门文章

最新文章