ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Запрос на получение диапазонов дат
15 сообщений из 15, страница 1 из 1
Запрос на получение диапазонов дат
    #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:04
| Ответить | Цитировать | Написать  
Запрос на получение диапазонов дат
    #39675657
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
Для Merchant - две одинаковые строки. Это как?
Будьте любезны, сформулируйте задачу нормально.
...
Рейтинг: 0 / 0
18.07.2018, 14:14
| Ответить | Цитировать | Написать  
Запрос на получение диапазонов дат
    #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:18
| Ответить | Цитировать | Написать  
Запрос на получение диапазонов дат
    #39675675
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Eneimor,

в колонке calldate хранятся данные, имеющие разный смысл?
...
Рейтинг: 0 / 0
18.07.2018, 14:31
| Ответить | Цитировать | Написать  
Запрос на получение диапазонов дат
    #39675682
Eneimor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Eneimor
Гость
Там хранится время, в которое был совершен звонок тем или иным лицом. Необходимо соотнести эти даты между одним и другим лицом в хронологическом порядке и без повторов
...
Рейтинг: 0 / 0
18.07.2018, 14:40
| Ответить | Цитировать | Написать  
Запрос на получение диапазонов дат
    #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:04
| Ответить | Цитировать | Написать  
Запрос на получение диапазонов дат
    #39675710
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Eneimor,

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

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

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

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

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


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