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

赵延东的一亩三分地 2023-04-14 原文

系列文章目录

文章目录

前言

本篇文章讲解的主要内容是：汇总报表时常要求按季度分类汇总这就需要通过给定年份获取对应的季度开始结束时间、业务数据不连续的情况下如何统计所有年份数据、如何统计相同月份与周内日期聘用的员工、如何返回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"写法。

总结

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

中时进阶 span class token 数据仓库计算季度开始结束时间非连续性数据统计相同月份非连续性

有关【SQL开发实战技巧】系列（二十）：数据仓库中时间类型操作（进阶）获取季度开始结束时间以及如何统计非连续性时间的数据的更多相关文章

ruby - 如何以所有可能的方式将字符串拆分为长度最多为 3 的连续子字符串？ - 2
我试图获取一个长度在1到10之间的字符串，并输出将字符串分解为大小为1、2或3的连续子字符串的所有可能方式。例如:输入:123456将整数分割成单个字符，然后继续查找组合。该代码将返回以下所有数组。[1,2,3,4,5,6][12,3,4,5,6][1,23,4,5,6][1,2,34,5,6][1,2,3,45,6][1,2,3,4,56][12,34,5,6][12,3,45,6][12,3,4,56][1,23,45,6][1,2,34,56][1,23,4,56][12,34,56][123,4,5,6][1,234,5,6][1,2,345,6][1,2,3,456][123
ruby - 解析 RDFa、微数据等的最佳方式是什么，使用统一的模式/词汇(例如 schema.org)存储和显示信息 - 2
我主要使用Ruby来执行此操作，但到目前为止我的攻击计划如下:使用gemsrdf、rdf-rdfa和rdf-microdata或mida来解析给定任何URI的数据。我认为最好映射到像schema.org这样的统一模式，例如使用这个yaml文件，它试图描述数据词汇表和opengraph到schema.org之间的转换:#SchemaXtoschema.orgconversion#data-vocabularyDV:name:namestreet-address:streetAddressregion:addressRegionlocality:addressLocalityphoto:i
ruby - Infinity 和 NaN 的类型是什么？ - 2
我可以得到Infinity和NaNn=9.0/0#=>Infinityn.class#=>Floatm=0/0.0#=>NaNm.class#=>Float但是当我想直接访问Infinity或NaN时:Infinity#=>uninitializedconstantInfinity(NameError)NaN#=>uninitializedconstantNaN(NameError)什么是Infinity和NaN？它们是对象、关键字还是其他东西？最佳答案您看到打印为Infinity和NaN的只是Float类的两个特殊实例的字符串
ruby - 检查方法参数的类型 - 2
我不确定传递给方法的对象的类型是否正确。我可能会将一个字符串传递给一个只能处理整数的函数。某种运行时保证怎么样？我看不到比以下更好的选择:defsomeFixNumMangler(input)raise"wrongtype:integerrequired"unlessinput.class==FixNumother_stuffend有更好的选择吗？最佳答案使用Kernel#Integer在使用之前转换输入的方法。当无法以任何合理的方式将输入转换为整数时，它将引发ArgumentError。defmy_method(number)
ruby-on-rails - Ruby 检查日期时间是否为 iso8601 并保存 - 2
我需要检查DateTime是否采用有效的ISO8601格式。喜欢:#iso8601?我检查了ruby是否有特定方法，但没有找到。目前我正在使用date.iso8601==date来检查这个。有什么好的方法吗？编辑解释我的环境，并改变问题的范围。因此，我的项目将使用jsapiFullCalendar，这就是我需要iso8601字符串格式的原因。我想知道更好或正确的方法是什么，以正确的格式将日期保存在数据库中，或者让ActiveRecord完成它们的工作并在我需要时间信息时对其进行操作。最佳答案我不太明白你的问题。我假设您想检查
ruby - 简单获取法拉第超时 - 2
有没有办法在这个简单的get方法中添加超时选项？我正在使用法拉第3.3。Faraday.get(url)四处寻找，我只能先发起连接后应用超时选项，然后应用超时选项。或者有什么简单的方法？这就是我现在正在做的:conn=Faraday.newresponse=conn.getdo|req|req.urlurlreq.options.timeout=2#2secondsend 最佳答案试试这个:conn=Faraday.newdo|conn|conn.options.timeout=20endresponse=conn.get(url
ruby - Ruby 有 `Pair` 数据类型吗？ - 2
有时我需要处理键/值数据。我不喜欢使用数组，因为它们在大小上没有限制(很容易不小心添加超过2个项目，而且您最终需要稍后验证大小)。此外，0和1的索引变成了魔数(MagicNumber)，并且在传达含义方面做得很差(“当我说0时，我的意思是head...”)。散列也不合适，因为可能会不小心添加额外的条目。我写了下面的类来解决这个问题:classPairattr_accessor:head,:taildefinitialize(h,t)@head,@tail=h,tendend它工作得很好并且解决了问题，但我很想知道:Ruby标准库是否已经带有这样一个类？最佳
ruby - 从 Ruby 中的主机名获取 IP 地址 - 2
我有一个存储主机名的Ruby数组server_names。如果我打印出来，它看起来像这样:["hostname.abc.com","hostname2.abc.com","hostname3.abc.com"]相当标准。我想要做的是获取这些服务器的IP(可能将它们存储在另一个变量中)。看起来IPSocket类可以做到这一点，但我不确定如何使用IPSocket类遍历它。如果它只是尝试像这样打印出IP:server_names.eachdo|name|IPSocket::getaddress(name)pnameend它提示我没有提供服务器名称。这是语法问题还是我没有正确使用类？输出:ge
ruby - 获取模块中定义的所有常量的值 - 2
我想获取模块中定义的所有常量的值:moduleLettersA='apple'.freezeB='boy'.freezeendconstants给了我常量的名字:Letters.constants(false)#=>[:A,:B]如何获取它们的值的数组，即["apple","boy"]？最佳答案为了做到这一点，请使用mapLetters.constants(false).map&Letters.method(:const_get)这将返回["a","b"]第二种方式:Letters.constants(false).map{|c
ruby-on-rails - 将 Ruby 中的日期/时间格式化为 YYYY-MM-DD HH :MM:SS - 2
这个问题在这里已经有了答案:Railsformattingdate(4个答案)关闭4年前。我想格式化Time.Now函数以显示YYYY-MM-DDHH:MM:SS而不是:“2018-03-0909:47:19+0000”该函数需要放在时间中.现在功能。require‘roo’require‘roo-xls’require‘byebug’file_name=ARGV.first||“Template.xlsx”excel_file=Roo::Spreadsheet.open(“./#{file_name}“,extension::xlsx)xml=Nokogiri::XML::Build