SQL：匹配日期范围

Question

我正在寻找随时间变化并由和 date字段定义的映射时，正在寻找最佳实践。例如，以此示例为例 postgres db db 使用员工信息。

它在员工和薪水之间有一个映射，可在不同的日期范围内有效：

> SELECT * FROM salaries LIMIT 5

|   emp_no |   salary | from_date   | to_date    |
|---------:|---------:|:------------|:-----------|
|    10001 |    60117 | 1986-06-26  | 1987-06-26 |
|    10001 |    62102 | 1987-06-26  | 1988-06-25 |
|    10001 |    66074 | 1988-06-25  | 1989-06-25 |
|    10001 |    66596 | 1989-06-25  | 1990-06-25 |
|    10001 |    66961 | 1990-06-25  | 1991-06-25 |

以及员工和头衔之间的映射：

> SELECT * FROM titles LIMIT 5

|   emp_no | title           | from_date   | to_date    |
|---------:|:----------------|:------------|:-----------|
|    10001 | Senior Engineer | 1986-06-26  | 9999-01-01 |
|    10002 | Staff           | 1996-08-03  | 9999-01-01 |
|    10003 | Senior Engineer | 1995-12-03  | 9999-01-01 |
|    10004 | Engineer        | 1986-12-01  | 1995-12-01 |
|    10004 | Senior Engineer | 1995-12-01  | 9999-01-01 |

将标题标题映射到薪水的最佳方法是什么，以便例如计算每个标题的平均薪水。请注意，雇员的薪水可能不会改变标题更改，反之亦然。

我目前的解决方案是将这些表与包含所有可能日期的表交叉加入：

WITH dates AS (
    SELECT date_trunc('day', dd):: date AS the_date
FROM generate_series
        ( '1985-01-01'::timestamp 
        , '2003-01-01'::timestamp
        , '1 day'::interval) dd
),
daily_salaries AS (
    SELECT
        emp_no,
        salary,
        the_date
    FROM salaries t
    CROSS JOIN dates d
    WHERE the_date BETWEEN from_date AND to_date
),
daily_titles AS (
    SELECT
        emp_no,
        title,
        the_date
    FROM titles t
    CROSS JOIN dates d
    WHERE the_date BETWEEN from_date AND to_date
)
SELECT
    title,
    AVG(salary) AS avg_salary
FROM daily_salaries
INNER JOIN daily_titles USING (emp_no, the_date)
GROUP BY 1
ORDER BY 2 DESC

但这似乎非常低效。做这件事的更好方法是什么？

---

编辑：正如卢克（Luuk）在答案中提到的那样，上面查询的问题是，十字架联接将产生大量记录。问题的症结在于，如何避免在每位员工的数天内扩大薪水和头衔，同时仍将员工的薪水与他们拥有头衔的时期相关联。

一般的问题是如何在开始和结束时定义的任意时间段，而无需每天在时间间隔内处理（如果可能）。

Answer 1

有点不清楚为什么您从2844047记录中选择（从薪水中选择计数（*）），然后将结果数乘以365（现有年度的每天），从而导致1038077155+记录的临时结果（+，因为有些一年的时间超过365天...）。最后，您正在计算avg_salary，在当年的365天中，这总是相同的。

我确实认为无需为所有单个日期生成_eries，并且此查询应给出正确的结果：

select 
   t2.title, 
   avg(avg_salary) 
from (select 
         emp_no, 
         avg(salary) avg_salary 
      from salaries 
      group by emp_no) x 
inner join titles t2 on t2.emp_no=x.emp_no 
group by 1 order by 2 desc;

在上述查询中，我没有从_date和/或to_date中选择，因为这将作为您的待办事项