时间戳

首页 > 解决方案 > 查找与 FIRST 非 0 值关联的 MIN 日期

sql - 查找与 FIRST 非 0 值关联的 MIN 日期

问题描述

我正在尝试生成一个经理开始日期列表，该列表可以由AS_OF表分区的最小日期确定。

我不确定如何以非处理繁重的方式完成此操作。我相信有一些 Windows 功能可以更适合实现这一目标。

我确实有以下工作，但速度非常慢。

SELECT
    Employee_ID,
    MIN(As_Of) as manager_start_date
FROM table
WHERE Direct_Reports > 0
GROUP BY 1

下面的示例表，底部有所需的输出。

+-------------+----------------+----------+
| Employee_ID | Direct_Reports | As_Of    |
+-------------+----------------+----------+
| 1           | 0              | 1/1/2019 |
+-------------+----------------+----------+
| 1           | 0              | 1/2/2019 |
+-------------+----------------+----------+
| 1           | 0              | 1/3/2019 |
+-------------+----------------+----------+
| 1           | 1              | 1/4/2019 |  '<--- First non 0 value for Employee 1'
+-------------+----------------+----------+
| 2           | 0              | 1/1/2019 |
+-------------+----------------+----------+
| 2           | 0              | 1/2/2019 |
+-------------+----------------+----------+
| 2           | 5              | 1/3/2019 |  '<--- First non 0 value for Employee 2'
+-------------+----------------+----------+
| 3           | 0              | 1/1/2019 |
+-------------+----------------+----------+
| 3           | 0              | 1/2/2019 |
+-------------+----------------+----------+
| 3           | 5              | 1/3/2019 |  '<--- First non 0 value for Employee 3'
+-------------+----------------+----------+
| 3           | 10             | 1/4/2019 |
+-------------+----------------+----------+
| 3           | 7              | 1/5/2019 |
+-------------+----------------+----------+

+-------------+--------------------+
| Employee_ID | Manager_Start_Date |
+-------------+--------------------+
| 1           | 1/4/2019           |
+-------------+--------------------+
| 2           | 1/3/2019           |
+-------------+--------------------+
| 3           | 1/3/2019           |
+-------------+--------------------+

标签： sqlpresto

解决方案

尝试这个：

select empid , min(case when directreport > 0 then as_of END) from dbo.manager
group by empid

推荐阅读