Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Запрос на получение диапазонов дат / 15 сообщений из 15, страница 1 из 1
18.07.2018, 14:04
    #39675647
Eneimor
Eneimor
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос на получение диапазонов дат
Добрый день, господа!

Долго искал сей вопрос, но подходящего ответа так и не нашел.
Есть таблица, куда ведется лог диалога клиента с заказчиком:

whocalled calldate tack_idUser 2017-06-17 17:30:08.360 12354Merchant 2017-06-17 17:36:15.910 12354User 2017-06-17 17:45:09.300 12354Merchant 2017-06-17 17:42:23.500 12354Merchant 2017-06-17 17:42:23.500 12354User 2017-06-17 17:38:03.202 12354

Необходимо написать запрос так, чтобы получалась таблица c диапазоном даты-времени в хронологической последовательности:

whocalled_1 calldate_1 calldate_2 whomcalled_2 task_idUser 2017-06-17 17:30:08.360 2017-06-17 17:36:15.910 Merchant 12354User 2017-06-17 17:38:03.202 2017-06-17 17:42:23.500 Merchant 12354

Обыскал всевозможные темы, но удовлетворяющего ответа, так и не нашел. Буду рад помощи.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.07.2018, 14:14
    #39675657
iap
iap
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос на получение диапазонов дат
Для Merchant - две одинаковые строки. Это как?
Будьте любезны, сформулируйте задачу нормально.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.07.2018, 14:18
    #39675660
Eneimor
Eneimor
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос на получение диапазонов дат
К сожалению, задача сформулирована именно так.
Мой вариант, который мне удалось найти, не исключает дублей, но необходимо, чтобы они не учитывались:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT DISTINCT a.[whocalled] AS 'User', 
a.[calldate] AS 'Begin dialog',
(SELECT DISTINCT MIN(b.[calldate]) FROM t1 b
WHERE b.[calldate] > a.[calldate] AND b.[calldate] = 'Merchant') AS 'End dialog',
b.[whocalled] as 'Merchant', 
a.[task_id] AS 'task_id'
FROM t1 a
LEFT JOIN t1 b ON a.[calldate] < b.[calldate] 
WHERE a.[whocalled] = 'User'
AND b.[whocalled] = 'Merchant'
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.07.2018, 14:31
    #39675675
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос на получение диапазонов дат
Eneimor,

в колонке calldate хранятся данные, имеющие разный смысл?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.07.2018, 14:40
    #39675682
Eneimor
Eneimor
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос на получение диапазонов дат
Там хранится время, в которое был совершен звонок тем или иным лицом. Необходимо соотнести эти даты между одним и другим лицом в хронологическом порядке и без повторов
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.07.2018, 15:04
    #39675699
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос на получение диапазонов дат
Код: sql
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.
26.
declare @t table (whocalled varchar(30), calldate datetime, task_id int)

insert into @t
values
('User', '20170617 17:30:08.360', 12354),
('Merchant', '20170617 17:36:15.910', 12354),
('User', '20170617 17:45:09.300', 12354),
('Merchant', '20170617 17:42:23.500', 12354),
('Merchant', '20170617 17:42:23.500', 12354),
('User', '20170617 17:38:03.202', 12354);

with a as
(
 select whocalled, calldate, task_id, row_number() over (partition by task_id order by calldate) as rn from @t where whocalled = 'User'
),
b as
(
 select whocalled, calldate, task_id, row_number() over (partition by task_id order by calldate) as rn from @t where whocalled = 'Merchant'
)
select
 a.whocalled, a.calldate, b.whocalled, b.calldate, a.task_id
from
 a join
 b on b.task_id = a.task_id and b.rn = a.rn and b.calldate > a.calldate
order by
 a.task_id, a.calldate;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.07.2018, 15:22
    #39675710
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос на получение диапазонов дат
Eneimor,

В таком случае нет доказательств связи между звонками. То есть вы их не можете поместить в один кортеж результирующего набора.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.07.2018, 16:56
    #39675778
Eneimor
Eneimor
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос на получение диапазонов дат
invm,

Ваш запрос работает безупречно, но в конечном итоге, если добавить еще записи, то все равно будет происходить смещение с из-за существующих дублей, и выводить он будет тот же самый результат, что и у меня. Но, тем не менее, большое спасибо за помощь.

Владислав Колосов,

Вы правы, судя по всему решить эту задачу именно по таким условиям не представляется возможным.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.07.2018, 17:03
    #39675787
Посетитель
Посетитель
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос на получение диапазонов дат
Eneimor,

дубли(если они не нужны) устраняются дистинктом
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.07.2018, 18:18
    #39675811
Руслан Дамирович
Руслан Дамирович
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос на получение диапазонов дат
Как-то так,
Код: sql
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.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
DECLARE
  @t TABLE (
    [whocalled] VARCHAR(30),
    [calldate] DATETIME,
    [task_id] INT )
;
INSERT
INTO
  @t
VALUES
  ( 'User',     '20170617 17:30:08.360', 12354 ),
  ( 'Merchant', '20170617 17:36:15.910', 12354 ),
  ( 'User',     '20170617 17:45:09.300', 12354 ),
  ( 'Merchant', '20170617 17:42:23.500', 12354 ),
  ( 'Merchant', '20170617 17:42:23.500', 12354 ),
  ( 'User',     '20170617 17:38:03.202', 12354 )
;
WITH
a AS (
  SELECT 
    [task_id],
    [whocalled],
    [calldate],
    [gr] = ROW_NUMBER() OVER ( PARTITION BY [task_id] ORDER BY [calldate] )
         - ROW_NUMBER() OVER ( PARTITION BY [task_id], [whocalled] ORDER BY [calldate] )
  FROM
    @t
),
b AS (
  SELECT
    [task_id],
    [calldate] = MIN( [calldate] ),
    [whocalled]
  FROM
    a
  GROUP BY
    [task_id],
    [whocalled],
    [gr]
),
c AS (
  SELECT
    [task_id],
    [calldate],
    [whocalled],
    [rn] = ROW_NUMBER() OVER ( PARTITION BY [task_id], [whocalled] ORDER BY [calldate] )
  FROM
    b
)
SELECT
  [task_id]    = ISNULL( c1.[task_id], c2.[task_id] ),
  [whocalled1] = c1.[whocalled],
  [calldate1]  = c1.[calldate],
  [whomcalled] = c2.[whocalled],
  [calldate2]  = c2.[calldate]
FROM
  ( SELECT * FROM c WHERE [whocalled] = 'User' ) c1
  FULL JOIN ( SELECT * FROM c WHERE [whocalled] = 'Merchant' ) c2 ON (
        c1.[task_id] = c2.[task_id]
    AND c1.[rn] = c2.[rn] )
;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.07.2018, 20:15
    #39675876
Собиратель мифов
Собиратель мифов
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос на получение диапазонов дат
Еще вариант.
Код: sql
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.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
DECLARE @t TABLE(whocalled VARCHAR(30) NOT NULL
                ,calldate DATETIME NOT NULL
                ,task_id INT NOT NULL);

INSERT INTO @t
VALUES('User', '20170617 17:30:08.360', 12354)
     ,('Merchant', '20170617 17:36:15.910', 12354)
     ,('User', '20170617 17:45:09.300', 12354)
     ,('Merchant', '20170617 17:42:23.500', 12354)
     ,('Merchant', '20170617 17:42:23.500', 12354)
     ,('User', '20170617 17:38:03.202', 12354);

WITH R -- исключить дубли
  AS (SELECT  DISTINCT
              [@t].whocalled
             ,[@t].calldate
             ,[@t].task_id
      FROM    @t)
    ,OrderedCalls -- пронумеровать в рамках задачи
  AS (SELECT
            whocalled
           ,calldate
           ,task_id
           ,ROW_NUMBER () OVER (PARTITION BY task_id ORDER BY calldate) AS n
      FROM  R)
    ,UserCalls -- отделить Users
  AS (SELECT
            whocalled
           ,calldate
           ,task_id
           ,n
      FROM  OrderedCalls
      WHERE (whocalled = 'User'))
    ,MerchantCalls -- отделить Merchants
  AS (SELECT
            whocalled
           ,calldate
           ,task_id
           ,n
      FROM  OrderedCalls
      WHERE (whocalled = 'Merchant'))
SELECT -- соединить данные User с Merchant
      UserCalls.whocalled
     ,UserCalls.calldate
     ,MerchantCalls.whocalled
     ,MerchantCalls.calldate
     ,MerchantCalls.task_id
FROM  UserCalls
      INNER JOIN MerchantCalls
        ON MerchantCalls.task_id = UserCalls.task_id
           AND MerchantCalls.n = UserCalls.n + 1;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.07.2018, 20:17
    #39675877
Собиратель мифов
Собиратель мифов
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос на получение диапазонов дат
Еще вариант.
Код: sql
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.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
DECLARE @t TABLE(whocalled VARCHAR(30) NOT NULL
                ,calldate DATETIME NOT NULL
                ,task_id INT NOT NULL);

INSERT INTO @t
VALUES('User', '20170617 17:30:08.360', 12354)
     ,('Merchant', '20170617 17:36:15.910', 12354)
     ,('User', '20170617 17:45:09.300', 12354)
     ,('Merchant', '20170617 17:42:23.500', 12354)
     ,('Merchant', '20170617 17:42:23.500', 12354)
     ,('User', '20170617 17:38:03.202', 12354);

WITH R -- исключить дубли
  AS (SELECT  DISTINCT
              [@t].whocalled
             ,[@t].calldate
             ,[@t].task_id
      FROM    @t)
    ,OrderedCalls -- пронумеровать в рамках задачи
  AS (SELECT
            whocalled
           ,calldate
           ,task_id
           ,ROW_NUMBER () OVER (PARTITION BY task_id ORDER BY calldate) AS n
      FROM  R)
    ,UserCalls -- отделить Users
  AS (SELECT
            whocalled
           ,calldate
           ,task_id
           ,n
      FROM  OrderedCalls
      WHERE (whocalled = 'User'))
    ,MerchantCalls -- отделить Merchants
  AS (SELECT
            whocalled
           ,calldate
           ,task_id
           ,n
      FROM  OrderedCalls
      WHERE (whocalled = 'Merchant'))
SELECT -- соединить данные User с Merchant
      UserCalls.whocalled
     ,UserCalls.calldate
     ,MerchantCalls.whocalled
     ,MerchantCalls.calldate
     ,MerchantCalls.task_id
FROM  UserCalls
      INNER JOIN MerchantCalls
        ON MerchantCalls.task_id = UserCalls.task_id
           AND MerchantCalls.n = UserCalls.n + 1;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.07.2018, 00:14
    #39675942
Deff
Deff
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос на получение диапазонов дат
EneimorК сожалению, задача сформулирована именно так.
Мой вариант, который мне удалось найти, не исключает дублей, но необходимо, чтобы они не учитывались:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT DISTINCT a.[whocalled] AS 'User', 
a.[calldate] AS 'Begin dialog',
(SELECT DISTINCT MIN(b.[calldate]) FROM t1 b
WHERE b.[calldate] > a.[calldate] AND b.[calldate] = 'Merchant') AS 'End dialog',
b.[whocalled] as 'Merchant', 
a.[task_id] AS 'task_id'
FROM t1 a
LEFT JOIN t1 b ON a.[calldate] < b.[calldate] 
WHERE a.[whocalled] = 'User'
AND b.[whocalled] = 'Merchant'

Зачем тут таблица B?

Задача типовая.
Решается через поле типа (select top 1 calldate from t1 b where b.task_id = a.task_id order by calldate desc).
Работает быстро при наличии нужного индекса.
А дубли легко удалить уже после.

Никаких DISTINCT, group by, и агрегатных функций для решения этой задачи.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.07.2018, 00:16
    #39675943
Deff
Deff
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос на получение диапазонов дат
Поправил.
where b.task_id = a.task_id and b.calldate <= a.calldate
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.07.2018, 17:22
    #39677040
Eneimor
Eneimor
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос на получение диапазонов дат
Просто шикарно, работает именно так, как надо. Спасибо, господа)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Запрос на получение диапазонов дат / 15 сообщений из 15, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]