|
11.03.2019, 15:02
#39784506
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
Участник
Откуда: Запорожье
Сообщения: 56 486
Рейтинг:
0
/ 0
|
|
|
|
DBAshnikпользовались ли вы авто-конверторами типа http://www.sqlines.com/online ? (если да, то какими? И как впечатления от них?)простой запрос не смог перевести
оракл 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
WITH T AS
(
SELECT 1 grp, DATE'2019-1-1' dfrom, DATE'2019-1-3'-INTERVAL '1' SECOND dto FROM dual UNION ALL
SELECT 1 grp, DATE'2019-1-1'+INTERVAL '1' HOUR dfrom, DATE'2019-1-2'-INTERVAL '18'HOUR - INTERVAL '1' SECOND dto FROM dual UNION ALL
SELECT 1 grp, DATE'2019-1-1'+INTERVAL '18'HOUR dfrom, DATE'2019-1-2'-INTERVAL '1' SECOND dto FROM dual UNION ALL
SELECT 1 grp, DATE'2019-1-1'+INTERVAL '20'HOUR dfrom, DATE'2019-1-3'-INTERVAL '1' SECOND dto FROM dual UNION ALL
SELECT 1 grp, DATE'2019-1-3' dfrom, DATE'2019-1-4'-INTERVAL '1' SECOND dto FROM dual UNION ALL
SELECT 1 grp, DATE'2019-1-3' dfrom, DATE'2019-1-5'-INTERVAL '1' SECOND dto FROM dual UNION ALL
SELECT 1 grp, DATE'2019-1-5' dfrom, DATE'2019-1-6'-INTERVAL '1' SECOND dto FROM dual UNION ALL
SELECT 2 grp, DATE'2019-1-1' dfrom, DATE'2019-1-2'-INTERVAL '1' SECOND dto FROM dual UNION ALL
SELECT 2 grp, DATE'2019-1-5' dfrom, DATE'2019-1-7'-INTERVAL '1' SECOND dto FROM dual UNION ALL
SELECT 2 grp, DATE'2019-1-6' dfrom, DATE'2019-1-8'-INTERVAL '1' SECOND dto FROM dual
)
SELECT grp, MAX(s), MIN(D) KEEP(DENSE_RANK FIRST ORDER BY s desc nulls last)
FROM
(
SELECT grp, inout, d,
SUM(inout) OVER(PARTITION BY grp ORDER BY D) s
FROM
(
SELECT grp, 1 inout, dfrom D FROM T WHERE dfrom IS NOT NULL UNION ALL
SELECT grp, -1 inout, dto d FROM T WHERE dto IS NOT NULL
)
)
GROUP BY grp
tsql 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
WITH T AS
(
SELECT 1 grp, DATE'2019-1-1' dfrom, DATE'2019-1-3'-INTERVAL '1' SECOND dto FROM dual UNION ALL
SELECT 1 grp, DATE'2019-1-1'+INTERVAL '1' HOUR dfrom, DATE'2019-1-2'-INTERVAL '18'HOUR - INTERVAL '1' SECOND dto UNION ALL
SELECT 1 grp, DATE'2019-1-1'+INTERVAL '18'HOUR dfrom, DATE'2019-1-2'-INTERVAL '1' SECOND dto UNION ALL
SELECT 1 grp, DATE'2019-1-1'+INTERVAL '20'HOUR dfrom, DATE'2019-1-3'-INTERVAL '1' SECOND dto UNION ALL
SELECT 1 grp, DATE'2019-1-3' dfrom, DATE'2019-1-4'-INTERVAL '1' SECOND dto UNION ALL
SELECT 1 grp, DATE'2019-1-3' dfrom, DATE'2019-1-5'-INTERVAL '1' SECOND dto UNION ALL
SELECT 1 grp, DATE'2019-1-5' dfrom, DATE'2019-1-6'-INTERVAL '1' SECOND dto UNION ALL
SELECT 2 grp, DATE'2019-1-1' dfrom, DATE'2019-1-2'-INTERVAL '1' SECOND dto UNION ALL
SELECT 2 grp, DATE'2019-1-5' dfrom, DATE'2019-1-7'-INTERVAL '1' SECOND dto UNION ALL
SELECT 2 grp, DATE'2019-1-6' dfrom, DATE'2019-1-8'-INTERVAL '1' SECOND dto
)
SELECT grp, MAX(s), MIN(D) KEEP(DENSE_RANK FIRST ORDER BY s desc nulls last)
FROM
(
SELECT grp, inout, d,
SUM(inout) OVER(PARTITION BY grp ORDER BY D) s
FROM
(
SELECT grp, 1 inout, dfrom D FROM T WHERE dfrom IS NOT NULL UNION ALL
SELECT grp, -1 inout, dto d FROM T WHERE dto IS NOT NULL
)
)
GROUP BY grp
только from dual выбросил |
|
|
