Т-SQL внешнего запроса применяются с вложенными подзапросами


У меня есть следующие 3 таблицы, и я не имею возможность изменить их.

  • [Logs] стол с [log_type], [log_time], [log_data]и других несущественных столбцов
  • [Places] стол с [name], [place_index]и других несущественных столбцов
  • [Addresses] стол с [place_index], [road_0], ...,[road_15]и других несущественных столбцов

Журналы [log_type] 12 или 15 имеет одну из дорог в [Addresses] стол в [log_data]; журналы [log_type] 1 имеет одно из имен из [Places] таблицы; журналы [log_type] 7 похож на тип 1, но с пробелом и некоторые другие данные после имени.

Какой запрос нужно сделать, чтобы выбрать список "12" или "15" бревна

  • не имеют "1" журнала, до того, которое соответствует месту (log_data), содержащий соответствующие дорожные.

или

  • имеет "1" журнала до него, но с "7" отчет между ними, что также соответствует тому же месту.

Например,

предполагая Road_X в Местня, Road_Y в PlaceJ, и Road_Ex, Road_Z в Плачек, и приведенный в следующей таблице.

--------------------------------------------
| id | log_type |  log_time  | log_data    |
--------------------------------------------
| 1  |    7     | 2018-01-30 |  PlaceA 2.3 |
--------------------------------------------
| 2  |    1     | 2018-01-30 |  PlaceB     |
--------------------------------------------
| 3  |    12    | 2018-01-31 |  Road_Ex    |
--------------------------------------------
| 4  |    7     | 2018-02-01 |  PlaceB 2.5 |
--------------------------------------------
| 5  |    7     | 2018-02-01 |  PlaceI 2.7 |
--------------------------------------------
| 6  |    15    | 2018-02-02 |  Road_X     |
--------------------------------------------
| 7  |    1     | 2018-02-04 |  PlaceI     |
--------------------------------------------
| 8  |    1     | 2018-02-05 |  PlaceJ     |
--------------------------------------------
| 9  |    12    | 2018-02-06 |  Road_X     |
--------------------------------------------
| 10 |    15    | 2018-02-06 |  Road_Y     |
--------------------------------------------
| 11 |    1     | 2018-02-10 |  PlaceK     |
--------------------------------------------
| 12 |    12    | 2018-02-11 |  Road_Z     |
--------------------------------------------
| 13 |    12    | 2018-02-11 |  Road_Ex    |
--------------------------------------------
| 14 |    7     | 2018-02-12 |  PlaceI 2.7 |
--------------------------------------------
| 15 |    7     | 2018-02-12 |  PlaceJ 2.8 |
--------------------------------------------
| 16 |    12    | 2018-02-17 |  Road_X     |
--------------------------------------------
| 17 |    15    | 2018-02-18 |  Road_Y     |
--------------------------------------------

Запрос должен вернуть строки 3, 6, 16, 17.

Это лучшее, что я смог придумать так далеко:

declare @since datetimeoffset
set @since = '2018-02-10-05:00'
select a.[log_type], a.[log_time], a.[log_data]
from 
    (select [log_type], [log_data], [log_time],
        (select [name] 
         from [Places]
         where [place_index] =
            (select [place_index] 
             from [Addresses] 
             where [log_data] in 
                ([road_0], [road_1], [road_2], [road_3], [road_4], 
                    [road_5], [road_6], [road_7], [road_8], [road_9],
                    [road_10], [road_11], [road_12], [road_13], [road_14], 
                    [road_15]))) as [place_name]
     from [Logs]
     where [log_type] in (12, 15) and [log_time] >= @since) a
outer apply 
    (select TOP 1 [log_data] from 
        (select TOP 1 [log_data], [log_time]
         from [Logs]
         where [log_type] = 1 and [log_data] = [place_name] 
            and [log_time] >= @since and [log_time] < a.[log_time]
         order by [log_time] desc
         UNION ALL
         (select TOP 1 NULL, [log_time]
          from [Logs]
          where [log_type] = 7 and LEFT([log_data], CHARINDEX(' ', [log_data]) - 1) = [place_name] 
          and [log_time] >= @since and [log_time] < a.[log_time]
          order by [log_time] desc)) i
     order by [log_time] desc) b
where b.[log_data] is null
order by a.[log_time] desc

Обратите внимание, что, потому что мне нужно ссылаться [place_name] из таблицы a в подзапросе, я должен был использовать outer apply вместо left join. Однако, это занимает 5 секунд, чтобы создать менее 40 записей из около 30к записей [Logs]. Я также попытался заменить outer apply С whereно нет заметной разницы.

Может ли запрос быть дополнительно оптимизирована или мне нужно перенести эту бизнес-логику на службу, которая отправляет этот запрос?


Редактировать

DDL для таблицы (только в том числе соответствующих столбцов):

CREATE TABLE [Logs](
    [log_time] [datetimeoffset](7) NOT NULL, -- indexed
    [log_type] [int] NULL,
    [log_data] [varchar](300) NULL
)

CREATE TABLE [Places](
    [place_index] [smallint] NOT NULL,       -- PK, indexed
    [name] [nchar](253) NULL
)

CREATE TABLE [Addresses](
    [place_index] [smallint] NOT NULL,       -- PK, indexed
    [road_0] [char](32) NULL,
    [road_1] [char](32) NULL,
    [road_2] [char](32) NULL,
    [road_3] [char](32) NULL,
    [road_4] [char](32) NULL,
    [road_5] [char](32) NULL,
    [road_6] [char](32) NULL,
    [road_7] [char](32) NULL,
    [road_8] [char](32) NULL,
    [road_9] [char](32) NULL,
    [road_10] [char](32) NULL,
    [road_11] [char](32) NULL,
    [road_12] [char](32) NULL,
    [road_13] [char](32) NULL,
    [road_14] [char](32) NULL,
    [road_15] [char](32) NULL
) 


194
0
задан 19 февраля 2018 в 03:02 Источник Поделиться
Комментарии
1 ответ

Вот это попытка, хотя я не могу проверить это работает, потому что вы не предоставили вставить скрипты и не включать столбец ID в DDL. Вы также не упомянуть, если журнал 1/7 является только на следующий журнал 12/15 на том же месте или если он применяется ко всем журнал 12/15 после того, как журнал 1/7 для этого место. Этот сценарий предполагает, что все.

DECLARE @since DATETIMEOFFSET
;

SET @since = '2018-02-10-05:00'
;

WITH PrevLogs AS
(
SELECT p.place_index, /* return the place index for log one */
One.log_time,
Seven.log_type
FROM dbo.Logs AS One
JOIN dbo.Places AS p
ON One.log_data = p.name
LEFT JOIN dbo.Logs AS Seven
ON Seven.log_data LIKE One.log_data + '%' /* Log data One matches the first portion of Log data Seven */
AND Seven.log_type = 7 /* Only match log Seven to the log One */
AND One.log_time < Seven.log_time /* Seven log has to be after the One log */
WHERE One.log_type = 1 /* Only return logs of type 1 */
),
LogsIWant AS
(
SELECT a.place_index, /* return the place index of the raods in log 12/15 */
l.log_time,
l.log_type,
l.log_data
FROM dbo.Logs AS l
JOIN dbo.Addresses AS a
ON l.log_data IN ( a.road_0, a.road_1, a.road_2, a.road_3, a.road_4, a.road_5, a.road_6, a.road_7, a.road_8, a.road_9, a.road_10, a.road_11, a.road_12, a.road_13, a.road_14, a.road_15 )
WHERE l.log_type IN ( 12, 15 )
AND l.log_time >= @since
)
SELECT liw.place_index,
liw.log_time,
liw.log_type,
liw.log_data,
pl.place_index,
pl.log_type
FROM LogsIWant AS liw
LEFT JOIN PrevLogs AS pl
ON liw.place_index = pl.place_index
AND pl.log_time < liw.log_time /* Log 1/7 are before Log 12/15 */
WHERE pl.place_index IS NULL /* Log 1 does not exist */
OR pl.log_type IS NOT NULL /* Log 1 & Log 7 exist */
;

На ваш вопрос может быть дополнительно оптимизирован, я бы ответил Не совсем. Эта структура нарушает столько принципы качества данных, это заняло бы меньше времени, чтобы объяснить, что он делает правильно.

Дважды за мою карьеру я решил сменить работу, а не мириться с мандатом, что "перемены-это не вариант". Если мне передали систему, которая была структурирована, как и говорил, я не мог сделать изменения, я бы сделал все, что мог , пока ищу другую работу.

Извини за грубость, но реальная проблема здесь-это ужасная модель данных.

Я не знаю, если добавить индекс рассчитывают как изменение, но простого улучшения вы могли бы некластерный индекс по столбцу имя место стол. Что бы повысить производительность объединений со стола журналы.

Удачи

1
ответ дан 22 февраля 2018 в 03:02 Источник Поделиться