【SQL开发实战技巧】系列(二十):数据仓库中时间类型操作(进阶)获取季度开始结束时间以及如何统计非连续性时间的数据

简介: 本篇文章讲解的主要内容是:***汇总报表时常要求按季度分类汇总这就需要通过给定年份获取对应的季度开始结束时间、业务数据不连续的情况下如何统计所有年份数据、如何统计相同月份与周内日期聘用的员工、如何返回2月或12月聘用的所有员工以及周二聘用的所有员工***

前言

本篇文章讲解的主要内容是:汇总报表时常要求按季度分类汇总这就需要通过给定年份获取对应的季度开始结束时间、业务数据不连续的情况下如何统计所有年份数据、如何统计相同月份与周内日期聘用的员工、如何返回2月或12月聘用的所有员工以及周二聘用的所有员工
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作,毕竟SQL开发在数据分析场景非常重要且基础,面试也会经常问SQL开发和调优经验,相信当我写完这一系列文章,也能再有所收获,未来面对SQL面试也能游刃有余~。


一、确定指定年份季度的开始日期和结束日期

生成汇总报表时常要求按季度分类汇总这就需要通过给定年份提取对应的季度信息,
那么问题来了,怎么快速获取某个季度的开始结束日期呢?
接下来给出解决方案:

SQL> with t as
  2   (select to_char(sysdate, 'yyyy') as 年, level as sn
  3      from dual
  4    connect by level <= 4
  5    )
  6  select sn as 季度,
  7         (sn - 1) * 3 + 1 as 开始月份,
  8         add_months(trunc(to_date(年, 'yyyy'), 'y'), (sn - 1) * 3) as 开始日期,
  9         add_months(trunc(to_date(年, 'yyyy'), 'y'), sn * 3) - 1 as 结束日期
 10    from t;

        季度       开始月份 开始日期    结束日期
---------- ---------- ----------- -----------
         1          1 2023-1-1    2023-3-31
         2          4 2023-4-1    2023-6-30
         3          7 2023-7-1    2023-9-30
         4         10 2023-10-1   2023-12-31

SQL>     

这种枚举季度信息的语句在写报表查询时可能会用到有必要记录下来备用。

二、补充时间范围内丢失的值

有时业务数据并不是连续的如下面的数据:

SQL> select empno,hiredate from emp order by 2;

EMPNO HIREDATE
----- -----------
 7369 1980-12-17
 7499 1981-2-20
 7521 1981-2-22
 7566 1981-4-2
 7698 1981-5-1
 7782 1981-6-9
 7844 1981-9-8
 7654 1981-9-28
 7839 1981-11-17
 7900 1981-12-3
 7902 1981-12-3
 7934 1982-1-23
 7788 1987-4-19
 7876 1987-5-23
 1001 2021-10-9 1

15 rows selected

有的年份没有招聘员工,这时按年份查询招聘人数结果如下:

SQL> select to_char(hiredate,'yyyy') as year, count(*) as cnt
  2  from emp
  3  group by to_char(hiredate,'yyyy')
  4  order by 1;

YEAR                                                                               CNT
--------------------------------------------------------------------------- ----------
1980                                                                                 1
1981                                                                                10
1982                                                                                 1
1987                                                                                 2
2021                                                                                 1

SQL> 

为了分析数据一般需要把表中没有的年份(如1983年)内的人数统计为0,这时就需要先根据表中的信息生成一个年份的枚举列表。

SQL> with t as
  2   (select extract(year from min(hiredate)) as 开始年份,
  3           extract(year from max(hiredate)) as 结束年份
  4      from emp where empno<>1001)
  5  select 开始年份 + (level - 1) as 年份
  6    from t
  7  connect by level <= ((结束年份 - 开始年份) + 1);

        年份
----------
      1980
      1981
      1982
      1983
      1984
      1985
      1986
      1987

8 rows selected

通过这个列表关联查询就可以得到所有年份的数据。

SQL> with t as
  2   (select extract(year from min(hiredate)) as 开始年份,
  3           extract(year from max(hiredate)) as 结束年份
  4      from emp
  5     where empno <> 1001),
  6  t1 as
  7   (select 开始年份 + (level - 1) as 年份
  8      from t
  9    connect by level <= ((结束年份 - 开始年份) + 1))
 10  select t1.年份, count(emp.empno) as 聘用人数
 11    from emp
 12   right join t1
 13      on (extract(year from emp.hiredate) = t1.年份)
 14   group by 年份
 15   order by 1;

        年份       聘用人数
---------- ----------
      1980          1
      1981         10
      1982          1
      1983          0
      1984          0
      1985          0
      1986          0
      1987          2

8 rows selected

三、按照给定的时间单位进行查找

有时需要查找特定的条件如要求返回2月或12月聘用的所有员工以及周二聘用的所有员工。

若要得到三个条件返回结果的合集用to_char函数分别确认雇佣日期是几月及周几再过滤就可以。

SQL> select ename,hiredate,to_char(hiredate,'day') as 星期
  2  from emp
  3  where to_char(hiredate,'mm') in('02','12')
  4  or to_char(hiredate,'d')='3';

ENAME      HIREDATE    星期
---------- ----------- ---------------------------------------------------------------------------
SMITH      1980-12-17  星期三
ALLEN      1981-2-20   星期五
WARD       1981-2-22   星期日
CLARK      1981-6-9    星期二
KING       1981-11-17  星期二
TURNER     1981-9-8    星期二
JAMES      1981-12-3   星期四
FORD       1981-12-3   星期四

8 rows selected

要点在于要避免字符集的影响如这里分别用to_char(hiredate,'mm')to_char(hiredate,'d')来生成与字符集无关的数值信息。

四、使用日期的特殊部分比较记录

在报表统计中常有同期对比的需求演示案例需求为:
统计相同月份与周内日期聘用的员工,如:有两个员工都是3月份周一聘用的则可以用分析函数计算次数然后进行过滤语句如下:

SQL> 
SQL> select ename,hiredate,to_char(hiredate,'mon day') as 星期
  2  from (
  3  select ename,hiredate,count(*)over(partition by to_char(hiredate,'mon day')) as ct
  4  from emp
  5  )
  6  where ct >1;

ENAME      HIREDATE    星期
---------- ----------- ---------------------------------------------------------------------------
FORD       1981-12-3   12月 星期四
JAMES      1981-12-3   12月 星期四

SQL> 

上述语句中要注意以下几点:

  • 我们使用了分析函数COUNT()over(),这样可以只访问一次emp就同时得到了明细(ename,hiredate)及汇总信息(cnt)。与分析函数之前需要两次访问emp的写法相比提高了效率。
  • 因为不需要过滤to_char函数的结果这里可以不必使用"mmd"写法。

总结

以上四种案例是仓库报表统计中常见又容易出错的几种场景,是博主几个粉丝网友提问的,博主总结发出来的!

相关文章
|
4月前
|
SQL 关系型数据库 MySQL
【MySQL】根据binlog日志获取回滚sql的一个开发思路
【MySQL】根据binlog日志获取回滚sql的一个开发思路
|
5月前
|
SQL Cloud Native 调度
云原生数据仓库使用问题之如何修改历史数据清理的SQL
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
|
1月前
|
SQL
开启慢SQL设置long_query_time=0.1为啥会统计的sql却存在小于100毫秒的sql
开启慢SQL设置long_query_time=0.1为啥会统计的sql却存在小于100毫秒的sql
30 1
|
2月前
|
SQL 存储 关系型数据库
mysql 数据库空间统计sql
mysql 数据库空间统计sql
47 0
|
3月前
|
SQL 安全 Go
SQL注入不可怕,XSS也不难防!Python Web安全进阶教程,让你安心做开发!
在Web开发中,安全至关重要,尤其要警惕SQL注入和XSS攻击。SQL注入通过在数据库查询中插入恶意代码来窃取或篡改数据,而XSS攻击则通过注入恶意脚本来窃取用户敏感信息。本文将带你深入了解这两种威胁,并提供Python实战技巧,包括使用参数化查询和ORM框架防御SQL注入,以及利用模板引擎自动转义和内容安全策略(CSP)防范XSS攻击。通过掌握这些方法,你将能够更加自信地应对Web安全挑战,确保应用程序的安全性。
96 3
|
4月前
|
SQL NoSQL 数据库
开发效率与灵活性:SQL vs NoSQL
【8月更文第24天】随着大数据和实时应用的兴起,数据库技术也在不断发展以适应新的需求。传统的SQL(结构化查询语言)数据库因其成熟的数据管理机制而被广泛使用,而NoSQL(Not Only SQL)数据库则以其灵活性和扩展性赢得了众多开发者的青睐。本文将从开发者的视角出发,探讨这两种数据库类型的优缺点,并通过具体的代码示例来说明它们在实际开发中的应用。
108 1
|
3月前
|
SQL 分布式计算 大数据
大数据开发SQL代码编码原则和规范
这段SQL编码原则强调代码的功能完整性、清晰度、执行效率及可读性,通过统一关键词大小写、缩进量以及禁止使用模糊操作如select *等手段提升代码质量。此外,SQL编码规范还详细规定了代码头部信息、字段与子句排列、运算符前后间隔、CASE语句编写、查询嵌套、表别名定义以及SQL注释的具体要求,确保代码的一致性和维护性。
104 0
|
4月前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL:主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异,为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统,均使用SQL作为查询语言,但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同,帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言,但每个系统都有独特的语法规则,了解这些差异有助于提升开发效率和项目成功率。
423 0
|
5月前
|
JSON 数据格式 SQL
SQL开发问题之直接使用join方法在处理字符串类型属性时可能会遇到性能问题如何解决
SQL开发问题之直接使用join方法在处理字符串类型属性时可能会遇到性能问题如何解决
|
5月前
|
SQL
SQL开发问题之使用distmapjoin的问题如何解决
SQL开发问题之使用distmapjoin的问题如何解决

热门文章

最新文章