Итоги с перекрестным применением

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

Настольные программы:

AIR_DATE              START_TIME    END_TIME
-------------------------------------------
1/26/2015 12:00:00 AM   27000000    28800000
1/26/2015 12:00:00 AM   28800000    32400000
1/26/2015 12:00:00 AM   34200000    34280000

Таблица рейтингов:

IMPRESSION_DATE   IMP_START_TIME    IMP_END_TIME  HH    F2_5  IMP_STATUS
--------------------------------------------------------------------
1/26/2015 12:00:00 AM   27000000    27899000      8685  4415   F
1/26/2015 12:00:00 AM   27900000    28799000      8690  4421   F 
1/26/2015 12:00:00 AM   28800000    29699000      9900  4410   F
1/26/2015 12:00:00 AM   29700000    30599000      9906  4414   F
1/26/2015 12:00:00 AM   30600000    31499000      6925  2580   F
1/26/2015 12:00:00 AM   31500000    32399000      6928  2588   F
1/26/2015 12:00:00 AM   32400000    33299000      9988  17     F
1/26/2015 12:00:00 AM   33300000    34199000      9998  19     F
1/26/2015 12:00:00 AM   34200000    35099000       781  2457   F

Я ищу, как получить целевой результат, установленный как:

AIR_DATE    START_TIME  END_TIME  IMP_DATE IMP_START_TIME  IMP_END_TIME  HH_F  F2_5_F
1/26/2015   27000000    28800000  1/26/2015      27000000   28799000  8687.5   4418
1/26/2015   28800000    30600000  1/26/2015     28800000    30599000 8414.75   3498
1/26/2015   34200000    34280000  1/26/2015     34200000    35099000     781   2457

Мой пример запроса, но он мне не помог :)

SELECT *
FROM PROGRAMS as P
    CROSS APPLY (
           SELECT 
              MIN(CASE WHEN R.IMP_STATUS = 'F' THEN IMP_START_TIME END) as IMP_START_TIME,
              MAX(CASE WHEN R.IMP_STATUS = 'F' THEN IMP_END_TIME END) as IMP_END_TIME,

              AVG(CASE WHEN R.IMP_STATUS = 'F' THEN HH END) as HH_F,
              AVG(CASE WHEN R.IMP_STATUS = 'F' THEN F2_5 END) as F2_5_F
            FROM RATINGS as R  
            WHERE R.IMPRESSION_DATE = P.AIR_DATE 
              AND R.IMP_START_TIME <= P.START_TIME 
              AND P.END_TIME <= R.IMP_END_TIME) as R

RDBS: MS SQL SERVER 2008 R2 Пример SQL Fiddler здесь: http://sqlfiddle.com/#!3/68fa5/2


sql sql-server cross-apply
person SpanishBoy    schedule 09.07.2015    source источник
comment
Похоже, вы неправильно указали границы в условии where в запросе. Обратите внимание на AND R.IMP_START_TIME ‹= P.START_TIME, похоже, должно быть AND R.IMP_START_TIME ›= P.START_TIME   -  person Ispirer SQLWays Migrations    schedule 09.07.2015
comment
Обновил исходные данные. Спасибо jpw   -  person SpanishBoy    schedule 09.07.2015

Ответы (2)


arrow_upward
2
arrow_downward

Если вы измените условие ограничения времени в кресте, применимо к:

WHERE R.IMPRESSION_DATE = P.AIR_DATE 
  AND 
  (
    (R.IMP_START_TIME >= P.START_TIME AND R.IMP_END_TIME <= P.END_TIME) 
    OR
    (R.IMP_START_TIME <= P.START_TIME AND R.IMP_END_TIME >= P.END_TIME)
  )

вы получите такой результат, как:

AIR_DATE    START_TIME  END_TIME    IMP_START_TIME  IMP_END_TIME        HH_F    F2_5_F
2015-01-26 00:00:00.000 27000000    28800000    27000000    28799000    8687    4418
2015-01-26 00:00:00.000 28800000    32400000    28800000    32399000    8414    3498
2015-01-26 00:00:00.000 34200000    34280000    34200000    35099000    781     2457

что вроде правильно :)

person jpw    schedule 09.07.2015
comment
Я не смог создать пример SQL Fiddler, но мой тест не проходит с этим ответом - person SpanishBoy; 09.07.2015
comment
@SpanishBoy О, хорошо. Можете ли вы указать, что не так с результатом? - person jpw; 09.07.2015
comment
К сожалению, это действительно работает - sqlfiddle.com/#!3/aa0fb/1. Спасибо! Можно ли получить другое решение без использования CROSS APPLY? - person SpanishBoy; 09.07.2015
comment
@SpanishBoy Это должно быть возможно, поскольку перекрестное соединение можно заменить внутренним соединением. - person jpw; 09.07.2015
comment
Похоже, проблема найдена - sqlfiddle.com/#!3/68fa5/2. Не подскажете там? - person SpanishBoy; 09.07.2015
comment
@SpanishBoy Я не могу понять логику того, какие строки следует считать вместе для достижения результатов. Не могли бы вы уточнить это в вопросе и, может быть, как-то пометить строки в рейтингах? - person jpw; 09.07.2015
comment
Давайте продолжим обсуждение в чате. - person SpanishBoy; 09.07.2015

arrow_upward
0
arrow_downward

Какая версия SQL Server? С 2008 года вы можете использовать OVER с агрегатными функциями, такими как MIN, MAX и AVG (https://msdn.microsoft.com/en-us/library/ms189461.aspx)

РЕДАКТИРОВАТЬ: добавить пример. Может быть, вы начнете с CTE и сделаете SELECT MIN/MAX/AVG(x) OVER() в финальном SELECT?

WITH myCTE AS
(
    SELECT pr.AIR_DATE,pr.START_TIME,pr.END_TIME
    FROM Programs AS pr
    INNER JOIN Ratings AS rt ON pr.AIR_DATE =rt.IMPRESSION_DATE
                            AND pr.START_TIME=rt.IMP_START_TIME
                            AND pr.END_TIME <= rt.IMP_END_TIME 
)
SELECT * FROM myCTE
person Shnugo    schedule 09.07.2015