请教各位一个算法问题

通垭口村的 · 发表于 2017-3-29 23:35

newkid 发表于 2017-3-29 23:33
如果是合并连续区间的问题，这个论坛已经出现过很多次了，比如：
http://www.itpub.net/thread-1223937-1- ...

好的，谢谢版主，我去学习下。

通垭口村的 · 发表于 2017-3-30 07:25

newkid 发表于 2017-3-29 23:33
如果是合并连续区间的问题，这个论坛已经出现过很多次了，比如：
http://www.itpub.net/thread-1223937-1- ...

版主如果既有连续也有不连续的应该怎么实现呢？比如有6条数据，前3条连续，或者有交叉，中间断了，后3条连续或者有交叉，我想把他们合并成2条数据。

newkid · 发表于 2017-3-30 08:46

通垭口村的发表于 2017-3-30 07:25
版主如果既有连续也有不连续的应该怎么实现呢？比如有6条数据，前3条连续，或者有交叉，中间断了，后3条 ...

http://www.itpub.net/forum.php?mod=viewthread&tid=1348543

通垭口村的 · 发表于 2017-3-30 08:57

newkid 发表于 2017-3-30 08:46
http://www.itpub.net/forum.php?mod=viewthread&tid=1348543

收到，谢谢

chengccy2010 · 发表于 2017-3-30 09:16

用蠢办法
WITH TMP(D,
DATA_DATE,
START_TIME,
END_TIME) AS
(SELECT '001', '20170329', '7:30', '8:30'
  FROM DUAL
  UNION ALL
  SELECT '001', '20170329', '8:00', '9:30'
  FROM DUAL
  UNION ALL
  SELECT '001', '20170329', '8:20', '8:40'
  FROM DUAL
  UNION ALL
  SELECT '001', '20170329', '10:15', '11:20'
  FROM DUAL
  UNION ALL
  SELECT '001', '20170329', '10:40', '12:50'
  FROM DUAL
  UNION ALL
  SELECT '001', '20170330', '7:40', '8:20'
  FROM DUAL
  UNION ALL
  SELECT '001', '20170330', '8:13', '9:14'
  FROM DUAL
  UNION ALL
  SELECT '001', '20170330', '8:20', '8:25'
  FROM DUAL
  UNION ALL
  SELECT '001', '20170330', '10:15', '11:20'
  FROM DUAL
  UNION ALL
  SELECT '001', '20170330', '10:40', '12:30'
  FROM DUAL
  UNION ALL
  SELECT '002', '20170330', '7:15', '8:20'
  FROM DUAL
  UNION ALL
  SELECT '002', '20170330', '8:13', '9:14'
  FROM DUAL
  UNION ALL
  SELECT '002', '20170330', '8:20', '8:40'
  FROM DUAL
  UNION ALL
  SELECT '002', '20170330', '10:15', '11:20'
  FROM DUAL
  UNION ALL
  SELECT '002', '20170330', '10:40', '12:10'
  FROM DUAL)
SELECT D, DATA_DATE, COUNT(DISTINCT S + (T2.COLUMN_VALUE - 1) / 24 / 60)
FROM (SELECT T.*,
            TO_DATE(DATA_DATE || ' ' || START_TIME, 'YYYY-MM-DD HH24:MI') AS S,
            TO_DATE(DATA_DATE || ' ' || END_TIME, 'YYYY-MM-DD HH24:MI') AS E
      FROM TMP T) T1,
   TABLE(CAST(MULTISET (SELECT LEVEL
                  FROM DUAL
                  CONNECT BY LEVEL <= (E - S) * 24 * 60) AS
               SYS.ODCINUMBERLIST)) T2
GROUP  BY D, DATA_DATE

通垭口村的 · 发表于 2017-3-30 13:05

本帖最后由通垭口村的于 2017-3-30 13:06 编辑

chengccy2010 发表于 2017-3-30 09:16
用蠢办法
WITH TMP(D,
DATA_DATE,

你好感谢你的回复，因为功力尚浅还在研究你的sql，现在公司需求有变，需要实现如下的效果，请教怎么实现呢：

id（公交车id） data_date（运营时间） hf_time (载客花费的时间，去除重复载客的时间)

001 20170329 155

001 20170329 120

001 20170330 94

001 20170330 135

002 20170330 119

002 20170330 115

也就是按空载时间把时间集合分层n个段，在分别计算各个段的花费时间。

lastwinner · 发表于 2017-3-30 14:02

请参考我的帖子 http://www.itpub.net/forum.php?m ... ypeid%26typeid%3D28

通垭口村的 · 发表于 2017-3-30 14:04

lastwinner 发表于 2017-3-30 14:02
请参考我的帖子 http://www.itpub.net/forum.php?mod=viewthread&tid=719692&extra=page%3D1%26filter%3Dt ...

好的，谢谢

chengccy2010 · 发表于 2017-3-30 14:09

通垭口村的发表于 2017-3-30 13:05
你好感谢你的回复，因为功力尚浅还在研究你的sql，现在公司需求有变，需要实现如下的效果，请教怎么实现 ...

可以继续在我之前的SQL上加工, 也可以换另外一只, 先判断出连续的区间, 取出最大最小时间,计算耗时即可;
WITH TMP(D,
DATA_DATE,
START_TIME,
END_TIME) AS
(SELECT '001', '20170329', '7:30', '8:30'
  FROM DUAL
  UNION ALL
  SELECT '001', '20170329', '8:00', '9:30'
  FROM DUAL
  UNION ALL
  SELECT '001', '20170329', '8:20', '8:40'
  FROM DUAL
  UNION ALL
  SELECT '001', '20170329', '10:15', '11:20'
  FROM DUAL
  UNION ALL
  SELECT '001', '20170329', '10:40', '12:50'
  FROM DUAL
  UNION ALL
  SELECT '001', '20170330', '7:40', '8:20'
  FROM DUAL
  UNION ALL
  SELECT '001', '20170330', '8:13', '9:14'
  FROM DUAL
  UNION ALL
  SELECT '001', '20170330', '8:20', '8:25'
  FROM DUAL
  UNION ALL
  SELECT '001', '20170330', '10:15', '11:20'
  FROM DUAL
  UNION ALL
  SELECT '001', '20170330', '10:40', '12:30'
  FROM DUAL
  UNION ALL
  SELECT '002', '20170330', '7:15', '8:20'
  FROM DUAL
  UNION ALL
  SELECT '002', '20170330', '8:13', '9:14'
  FROM DUAL
  UNION ALL
  SELECT '002', '20170330', '8:20', '8:40'
  FROM DUAL
  UNION ALL
  SELECT '002', '20170330', '10:15', '11:20'
  FROM DUAL
  UNION ALL
  SELECT '002', '20170330', '10:40', '12:10'
  FROM DUAL)
SELECT D, DATA_DATE, MIN(S) AS S, MAX(E) AS E,
   (MAX(E) - MIN(S)) * 24 * 60 AS HF_TIME
FROM (SELECT D, DATA_DATE, S, E,
            SUM(FLAG) OVER(PARTITION BY D, DATA_DATE ORDER BY S) FLAG
      FROM (SELECT D, DATA_DATE, S, E,
                     CASE
                        WHEN S <= LAG(E)
                           OVER(PARTITION BY D, DATA_DATE ORDER BY S) THEN
                           0
                        ELSE
                           1
                        END FLAG
               FROM (SELECT D, DATA_DATE,
                              TO_DATE(DATA_DATE || ' ' || START_TIME,
                                       'YYYY-MM-DD HH24:MI') AS S,
                              TO_DATE(DATA_DATE || ' ' || END_TIME,
                                       'YYYY-MM-DD HH24:MI') AS E
                        FROM TMP)))
GROUP  BY D, DATA_DATE, FLAG
ORDER  BY D, DATA_DATE, S

通垭口村的 · 发表于 2017-3-30 14:11

chengccy2010 发表于 2017-3-30 14:09
可以继续在我之前的SQL上加工, 也可以换另外一只, 先判断出连续的区间, 取出最大最小时间,计算耗时即可;
...

好的，谢谢