Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Запрос на получение диапазонов дат  [new]
Eneimor
Member

Откуда:
Сообщений: 9
Добрый день, господа!

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

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


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

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


Обыскал всевозможные темы, но удовлетворяющего ответа, так и не нашел. Буду рад помощи.
18 июл 18, 14:04    [21581206]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на получение диапазонов дат  [new]
iap
Member

Откуда: Москва
Сообщений: 46977
Для Merchant - две одинаковые строки. Это как?
Будьте любезны, сформулируйте задачу нормально.
18 июл 18, 14:14    [21581268]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на получение диапазонов дат  [new]
Eneimor
Member

Откуда:
Сообщений: 9
К сожалению, задача сформулирована именно так.
Мой вариант, который мне удалось найти, не исключает дублей, но необходимо, чтобы они не учитывались:

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'
18 июл 18, 14:18    [21581288]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на получение диапазонов дат  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7746
Eneimor,

в колонке calldate хранятся данные, имеющие разный смысл?
18 июл 18, 14:31    [21581351]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на получение диапазонов дат  [new]
Eneimor
Member

Откуда:
Сообщений: 9
Там хранится время, в которое был совершен звонок тем или иным лицом. Необходимо соотнести эти даты между одним и другим лицом в хронологическом порядке и без повторов
18 июл 18, 14:40    [21581407]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на получение диапазонов дат  [new]
invm
Member

Откуда: Москва
Сообщений: 9343
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;
18 июл 18, 15:04    [21581520]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на получение диапазонов дат  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7746
Eneimor,

В таком случае нет доказательств связи между звонками. То есть вы их не можете поместить в один кортеж результирующего набора.
18 июл 18, 15:22    [21581648]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на получение диапазонов дат  [new]
Eneimor
Member

Откуда:
Сообщений: 9
invm,

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

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

Вы правы, судя по всему решить эту задачу именно по таким условиям не представляется возможным.
18 июл 18, 16:56    [21581961]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на получение диапазонов дат  [new]
Посетитель
Member

Откуда:
Сообщений: 1209
Eneimor,

дубли(если они не нужны) устраняются дистинктом
18 июл 18, 17:03    [21581993]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на получение диапазонов дат  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Как-то так,
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] )
;
18 июл 18, 18:18    [21582194]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на получение диапазонов дат  [new]
Собиратель мифов
Member

Откуда:
Сообщений: 10
Еще вариант.
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;
18 июл 18, 20:15    [21582495]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на получение диапазонов дат  [new]
Собиратель мифов
Member

Откуда:
Сообщений: 10
Еще вариант.
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;
18 июл 18, 20:17    [21582498]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на получение диапазонов дат  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18326
Eneimor
К сожалению, задача сформулирована именно так.
Мой вариант, который мне удалось найти, не исключает дублей, но необходимо, чтобы они не учитывались:

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, и агрегатных функций для решения этой задачи.
19 июл 18, 00:14    [21583045]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на получение диапазонов дат  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18326
Поправил.
where b.task_id = a.task_id and b.calldate <= a.calldate
19 июл 18, 00:16    [21583050]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на получение диапазонов дат  [new]
Eneimor
Member

Откуда:
Сообщений: 9
Просто шикарно, работает именно так, как надо. Спасибо, господа)
20 июл 18, 17:22    [21589598]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить