Как лучше всего сопоставить три таблицы БД с уникальными отметками времени?

У меня есть три таблицы, в которые в течение дня поступает новая информация из трех источников данных.

Table A     Table B     Table C
5, 8:00     J, 8:00     3, 8:00
6, 8:01     K, 8:02     8, 8:04
4, 8:03
9, 8:06

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

Table A     Table B     Table C     *Data As of*
5, 8:00     J, 8:00     3, 8:00     *8:00*
6, 8:01     J, 8:00     3, 8:00     *8:01*
6, 8:01     K, 8:02     3, 8:00     *8:02*
4, 8:03     K, 8:02     3, 8:00     *8:03*
4, 8:03     K, 8:02     8, 8:04     *8:04*
9, 8:06     K, 8:02     8, 8:04     *8:06*

В настоящее время я сбрасываю 3 запроса в 3 таблицы данных. Затем я перебираю все три сразу, беря самую раннюю временную метку из трех. Это работает, но немного громоздко. В одной таблице содержится около 3 миллионов записей в день, в одной - 200, а в одной - несколько. Иногда я обрабатываю данные за 20 дней. Мысли о лучшем подходе?


sql c# sql-server
person Soma Holiday    schedule 03.03.2015    source источник
comment
Вам нужен результат сразу для всех трех таблиц или можно использовать один запрос для каждой таблицы?   -  person Daniel Hilgarth    schedule 03.03.2015
comment
Вы как-то дублируете строки в результате? Что решает, что 6 и 4 в A дважды, или K в B четыре раза?   -  person James Z    schedule 03.03.2015
comment
О, я думаю, я понял, вы хотите результат для каждой минуты, которая берет последнюю строку из каждой таблицы, где время является контрольным временем или меньше?   -  person James Z    schedule 03.03.2015
comment
@JamesZ На самом деле это для каждого уникального раза среди 3 столов. Обратите внимание, что 8:05 нет ни в таблицах, ни в результатах.   -  person juharr    schedule 03.03.2015
comment
Мне нужны сразу все три части информации. В примере используются четные секунды, но, учитывая размер таблицы, очевидно, что существуют временные метки с точностью до миллисекунды.   -  person Soma Holiday    schedule 03.03.2015
comment
Вы можете использовать rownumber, чтобы перечислить каждую строку из каждой таблицы, упорядоченной по метке времени, и присоединиться к basead в этом порядке   -  person jean    schedule 03.03.2015

Ответы (2)


arrow_upward
2
arrow_downward

Это по крайней мере один из способов сделать это. Вероятно, потребуется некоторый анализ производительности, но это предполагает, что вы создаете таблицу с разными временами, которые у вас есть. Если этого недостаточно для минутного уровня (или того, что у вас есть на столе), вы, конечно, можете сделать "вставить в # время, выбрать отдельное время ..." из каждой таблицы перед запуском этого, но это может быть довольно тоже тяжелый.

select distinct
  a.id as a_id,
  a.time as a_time,
  b.id as b_id,
  b.time as b_time,
  c.id as c_id,
  c.time as c_time
from
  time t

  outer apply (
    select top 1 id, time
    from tablea a
    where a.time <= t.time
    order by a.time desc
  ) a

  outer apply (
    select top 1 id, time
    from tableb b
    where b.time <= t.time
    order by b.time desc
  ) b

  outer apply (
    select top 1 id, time
    from tablec c
    where c.time <= t.time
    order by c.time desc
  ) c

order by 
  a_time, 
  b_time, 
  c_time

SQL Fiddle: http://sqlfiddle.com/#!3/de7ae/6

person James Z    schedule 03.03.2015

arrow_upward
0
arrow_downward

Попробуйте следующий сценарий (SQL2012 +):

-- Step #1: it creates a table to store all distinct TS
CREATE TABLE #AllTS (TS DATETIME NOT NULL PRIMARY KEY) -- Change type of TS column with the proper data type

-- Step #2: it inserts distinct (UNION) TS values
INSERT  #AllTS
SELECT  TS
FROM (
    SELECT TS FROM dbo.A
    UNION SELECT TS FROM dbo.B
    UNION SELECT TS FROM dbo.C
) x(TS)

-- Step #3: for every source table use bellow query to generate requested resultset
SELECT  MAX(y.Col1)OVER(PARTITION BY GroupID) AS Col1,
        MAX(y.TS)OVER(PARTITION BY GroupID) AS TS
FROM (
    SELECT  a.Col1, a.TS, SUM(CASE WHEN a.TS IS NOT NULL THEN 1 ELSE 0 END) OVER(ORDER BY x.TS) AS GroupID
    FROM    #AllTS x LEFT JOIN dbo.A a ON x.TS = a.TS
) y

Примечание 1. Вам следует попытаться ускорить выполнение вышеуказанного запроса, используя индексы, созданные для каждой исходной таблицы в столбце TS. Пример:

CREATE INDEX IX_A_TS_#_Col1 ON dbo.A(TS) INCLUDE (Col1)

Примечание 2: Кроме того, для повышения производительности последнего запроса вы можете протестировать различные совместные подсказки:

#AllTS x LEFT HASH JOIN dbo.A -- Could be useful  when source tables are "big"

or

#AllTS x MERGE JOIN dbo.A

Демо

person Bogdan Sahlean    schedule 03.03.2015