代码之家 › 专栏 › 技术社区 › LetEpsilonBeLessThanZero

在Oracle数据库中,有什么比“helper”表更好的选择?

oracle sql

LetEpsilonBeLessThanZero · 技术社区 · 7 年前

假设我有一个“员工”表,上面有员工的开始和结束日期,比如:

员工

employee_id   start_date   end_date
53            '19901117'   '99991231'
54            '19910208'   '20010512'
55            '19910415'   '20120130'
.             .            .
.             .            .
.             .            .

比如说,我想每月统计月底受雇的员工人数。所以我要处理的结果数据集如下:

month        count of employees
'20150131'   120
'20150228'   118
'20150331'   122
.            .
.            .
.            .

我目前知道如何执行此操作的最佳方法是创建一个“helper”表来联接,例如:

黑尔佩尔塔

month
'20150131'
'20150228'
'20150331'
.
.
.

然后执行如下查询:

SELECT t0b.month,
        count(t0a.employee_id)
FROM employees t0a
JOIN helper_tbl t0b
ON t0b.month BETWEEN t0a.start_date AND t0a.end_date
GROUP BY t0b.month

然而,这对我来说有些烦人的解决方案,因为这意味着我必须一直创建这些小助手表,它们会扰乱我的模式。我觉得其他人对“助手”表的需求是一样的,但我猜人们已经想出了一个更好的方法来解决这个问题,这不是很手动的。或者你们真的像我一样一直在创建“助手”表来避开这些情况吗?

我知道这个问题对于堆栈溢出有点开放性,所以让我提供一个更封闭的问题版本,即, 只考虑“雇员”表,您将如何获取我上面显示的结果数据集? “

2 回复 | 直到 7 年前

Alex Poole 7 年前

您可以使用CTE生成所有月份值,要么形成固定的起始点,要么基于表中的最早日期:

with months (month) as (
  select add_months(first_month, level - 1)
  from (
    select trunc(min(start_date), 'MM') as first_month from employees
  )
  connect by level <= ceil(months_between(sysdate, first_month))
)
select * from months;

使用最早开始日期为1990-11-17的数据(如您的示例中所示),可以生成333行:

MONTH              
-------------------
1990-11-01 00:00:00
1990-12-01 00:00:00
1991-01-01 00:00:00
1991-02-01 00:00:00
1991-03-01 00:00:00
...
2018-06-01 00:00:00
2018-07-01 00:00:00

然后,可以在联接到表的查询中使用它,例如:

with months (month) as (
  select add_months(first_month, level - 1)
  from (
    select trunc(min(start_date), 'MM') as first_month from employees
  )
  connect by level <= ceil(months_between(sysdate, first_month))
)
select m.month, count(*) as employees
from months m
left join employees e
on e.start_date <= add_months(m.month, 1)
and (e.end_date is null or e.end_date >= add_months(m.month, 1))
group by m.month
order by m.month;

假设您希望包括仍有工作的人,所以您需要允许结束日期为空(除非您正在为仍有工作的人使用神奇的结束日期值…)

将日期存储为字符串会更复杂一些,但您可以用类似的方式生成月份信息:

with months (month, start_date, end_date) as (
  select add_months(first_month, level - 1),
    to_char(add_months(first_month, level - 1), 'YYYYMMDD'),
    to_char(last_day(add_months(first_month, level - 1)), 'YYYYMMDD')
  from (
    select trunc(min(to_date(start_date, 'YYYYMMDD')), 'MM') as first_month from employees
  )
  connect by level <= ceil(months_between(sysdate, first_month))
)
select m.month, m.start_date, m.end_date, count(*) as employees
from months m
left join employees e
on e.start_date <= m.end_date
and (e.end_date is null or e.end_date > m.end_date)
group by m.month, m.start_date, m.end_date
order by m.month;

用少量的数据进行了非常轻微的测试,两种方法似乎都有效。

eifla001 7 年前

如果您想得到月底受雇的员工,则可以在查询的where子句中使用last-day函数。此外,还可以在查询的group by子句中使用该函数。所以您的查询如下:

SELECT LAST_DAY(start_date), COUNT(1)
  FROM employees
 WHERE start_date = LAST_DAY(start_date)
 GROUP BY LAST_DAY(start_date)

或者,如果您只想计算每月雇用的员工数,请使用下面的查询。

SELECT LAST_DAY(start_date), COUNT(1)
  FROM employees
 GROUP BY LAST_DAY(start_date)