我需要为 case 编写一个SQL查询，但在查询的末尾，我看不到我的数据.
情况是这样的
The process of handling development requests from business units is carried out on the system. Each request goes through the following stages before being completed.
Stage 1 - Manager Approval: Approval by the employee's manager who initiated the request.
Stage 2 - Analysis: Conducting analysis work and completing the analysis document.
Stage 3 - Analysis Approval: Sending the analysis document for approval to the request owner.
Stage 4 - Development: Development work.
Stage 5 - Testing: Testing work.
Stage 6 - Completed: Records that have completed the testing phase wait in the Completed stage.
The IT team wishes to measure the average completion time for a request. The time spent by the record in the IT department will start after "Manager Approval" is granted, and the time spent during the "Analysis Approval" stage will not be included in the IT duration. A report in the following format regarding business unit times is requested:
Month | Average Days (Monthly) | Average Days (Yearly) |
March 2019 | | |
January 2019 | | |
February 2019 | | |
MONTH: Represents the month in which the request was closed. For January 2019, the average values of requests completed in January 2019 will be displayed.
AVERAGE DAYS (MONTHLY): The average completion time of requests completed in the respective month.
AVERAGE DAYS (YEARLY): The average completion time of requests completed from the beginning of the year until the end of the respective month.
我这么做了，但结果空着桌子.
WITH CTE AS (
SELECT
request_id,
TO_TIMESTAMP(start, 'MM/DD/YY HH24:MI') AS start_time,
LEAD(TO_TIMESTAMP(start, 'MM/DD/YY HH24:MI')) OVER (PARTITION BY request_id ORDER BY TO_TIMESTAMP(start, 'MM/DD/YY HH24:MI')) AS next_start_time
FROM mytable
WHERE stage = 'MANAGER_APPROVAL'
)
SELECT
TO_CHAR(DATE_TRUNC('month', start_time), 'Mon YYYY') AS "Month",
ROUND(AVG(EXTRACT(EPOCH FROM (next_start_time - start_time)) / 86400)::NUMERIC, 2) AS "Average IT Duration (Monthly)",
ROUND(AVG(EXTRACT(EPOCH FROM (next_start_time - start_time)) / 86400)::NUMERIC * 12, 2) AS "Average IT Duration (Yearly)"
FROM CTE
WHERE next_start_time IS NOT NULL
GROUP BY "Month"
ORDER BY "Month";
下面是我的表中的一些样例行.
id request_id stage start finish
1 5052047 MANAGER_APPROVAL 1/2/19 11:45 1/2/19 12:11
2 5052047 ANALYSIS 1/2/19 12:11 1/10/19 20:00
3 5052047 ANALYSIS_APPROVAL 1/10/19 20:00 1/11/19 8:57
4 5052047 ANALYSIS 1/11/19 8:57 1/11/19 9:17
5 5052047 ANALYSIS_APPROVAL 1/11/19 9:17 1/15/19 10:50
6 5052047 DEVELOPMENT 1/15/19 10:50 2/4/19 12:21
7 5052047 TEST 2/4/19 12:21 2/11/19 10:48
8 5052047 COMPLETED 2/11/19 10:48 NULL