在oracle plsql中计算每个项目在每个站点花费的天数

Question

日志表如下:

| prod_id  | station_id |             date_in          |
| -------- | ---------- | ---------------------------- |
| p1       |    s1      |  2022-09-01 12:06:41.6216195 |
| p2       |    s1      |  2022-09-02 10:06:14.6216195 |
| p2       |    s2      |  2022-09-02 02:04:55.6216195 |
| p1       |    s2      |  2022-09-02 11:06:40.6216195 |
| p3       |    s1      |  2022-09-02 04:06:23.6216195 |
| p1       |    s3      |  2022-09-03 12:00:33.6216195 |
| p2       |    s1      |  2022-09-04 02:06:44.6216195 |
| p1       |    s4      |  2022-09-04 07:12:20.6216195 |
| p2       |    s2      |  2022-09-05 03:04:21.6216195 |
| p2       |    s3      |  2022-09-07 05:17:35.6216195 |
| p1       |    s3      |  2022-09-08 14:50:54.6216195 |
| p1       |    s4      |  2022-09-10 09:08:10.6216195 |
| p1       |    s5      |  2022-09-11 11:22:47.6216195 |

如何计算每个产品在每个站点花费的总时间(以天为单位)?

例如，计算p1花费的天数:

| prod_id  | station_id |    date_in   |       sysdate = (2022-09-13)       |
| -------- | ---------- | ------------ | ---------------------------------- |
| p1       |    s1      |  2022-09-01  |  (2022-09-02) - (2022-09-01) = 1   |
| p1       |    s2      |  2022-09-02  |  (2022-09-03) - (2022-09-02) = 1   |  
| p1       |    s3      |  2022-09-03  |  (2022-09-04) - (2022-09-03) = 1   |
| p1       |    s4      |  2022-09-04  |  (2022-09-08) - (2022-09-04) = 2   |
| p1       |    s3      |  2022-09-08  |  (2022-09-10) - (2022-09-08) = 2   |
| p1       |    s4      |  2022-09-10  |  (2022-09-11) - (2022-09-10) = 2   |
| p1       |    s5      |  2022-09-11  |     sysdate   - (2022-09-11) = 2   |

result for p1:

| prod_id  | s1 | s2 | s3  |  s4 | s5 | s6 |...
| -------- | -- | -- | --  |  -- | -- | -- |...
|    p1    |  1 |  1 | 1+2 | 2+2 |  2 |  0 |...   

最后的结果应该是这样的:

| prod_id  | s1 | s2 | s3 | s4 | s5 | s6 |...
| -------- | -- | -- | -- | -- | -- | -- |...
|    p1    |  1 |  1 |  3 |  4 |  2 |  0 |...
|    p2    |  1 |  4 |  0 |  0 |  0 |  0 |...
|    p3    | 11 |  0 |  0 |  0 |  0 |  0 |...

对于每个产品的最后一条记录，应考虑sysdate进行计算。

站点没有任何特定的顺序(日志表只能按date_in排序)。

我正在使用oracle 11g.

Answer 1

您可以使用LEAD解析函数找到与下一个日期的差值，然后找到与PIVOT的天数:

SELECT prod_id,
COALESCE(s1, 0) AS s1,
COALESCE(s2, 0) AS s2,
COALESCE(s3, 0) AS s3,
COALESCE(s4, 0) AS s4,
COALESCE(s5, 0) AS s5,
COALESCE(s6, 0) AS s6
FROM   (
SELECT prod_id,
station_id,
ROUND(
EXTRACT(DAY FROM diff)
+ EXTRACT(HOUR FROM diff)/24
+ EXTRACT(MINUTE FROM diff)/24/60
+ EXTRACT(SECOND FROM diff)/24/60/60,
5
) AS days_diff
FROM   (
SELECT prod_id,
station_id,
LEAD(date_in, 1, SYSDATE) OVER (
PARTITION BY prod_id
ORDER BY date_in
) - date_in AS diff
FROM   table_name
)
)
PIVOT (
SUM(days_diff)
FOR station_id IN (
's1' AS s1,
's2' AS s2,
's3' AS s3,
's4' AS s4,
's5' AS s5,
's6' AS s6
)
)

对于您的样本数据:

CREATE TABLE table_name ( prod_id, station_id, date_in ) AS
SELECT 'p1', 's1', TIMESTAMP '2022-09-01 12:06:41.6216195' FROM DUAL UNION ALL
SELECT 'p2', 's1', TIMESTAMP '2022-09-02 10:06:14.6216195' FROM DUAL UNION ALL
SELECT 'p2', 's2', TIMESTAMP '2022-09-02 02:04:55.6216195' FROM DUAL UNION ALL
SELECT 'p1', 's2', TIMESTAMP '2022-09-02 11:06:40.6216195' FROM DUAL UNION ALL
SELECT 'p3', 's1', TIMESTAMP '2022-09-02 04:06:23.6216195' FROM DUAL UNION ALL
SELECT 'p1', 's3', TIMESTAMP '2022-09-03 12:00:33.6216195' FROM DUAL UNION ALL
SELECT 'p2', 's1', TIMESTAMP '2022-09-04 02:06:44.6216195' FROM DUAL UNION ALL
SELECT 'p1', 's4', TIMESTAMP '2022-09-04 07:12:20.6216195' FROM DUAL UNION ALL
SELECT 'p2', 's2', TIMESTAMP '2022-09-05 03:04:21.6216195' FROM DUAL UNION ALL
SELECT 'p2', 's3', TIMESTAMP '2022-09-07 05:17:35.6216195' FROM DUAL UNION ALL
SELECT 'p1', 's3', TIMESTAMP '2022-09-08 14:50:54.6216195' FROM DUAL UNION ALL
SELECT 'p1', 's4', TIMESTAMP '2022-09-10 09:08:10.6216195' FROM DUAL UNION ALL
SELECT 'p1', 's5', TIMESTAMP '2022-09-11 11:22:47.6216195' FROM DUAL

输出:

S6958321.037422.561845.4119378.06039000