SQL как создать подробную таблицу из сводной таблицы

Теперь у меня есть итоговая таблица игр вроде этого:

Суммарная таблица

И я хочу разделить «время начала» и «время окончания» по часам и преобразовать их в следующий подробный вид:

Мне это нужно

Это похоже на Unpivot, но не совсем то же самое. Как мне написать инструкцию sql? Большое спасибо !!


sql unpivot row summary
person Echo    schedule 30.08.2012    source источник
comment
Что вы пробовали? Если вы публикуете то, что пробовали, люди часто будут более склонны помочь. Мы не хотим делать за них работу людей, но мы поможем, если в SQL есть что-то, на чем вы застряли.   -  person N West    schedule 30.08.2012
comment
Какую СУБД и версию вы используете?   -  person Lamak    schedule 30.08.2012
comment
Я использую SQL Server 2008, спасибо !!   -  person Echo    schedule 30.08.2012
comment
Я пробовал использовать переменные, чтобы рассчитать, сколько раз может потребоваться повторение одной записи. Но я не знаю, как автоматически вставить измененную запись. Кроме того, мне нужно удалить переменную позже, потому что SSAS не позволит использовать переменные как T-SQL.   -  person Echo    schedule 30.08.2012

Ответы (3)


arrow_upward
3
arrow_downward

В PostgreSQL это:

SELECT PlayerID
     , generate_series(s.StartTime, s.EndTime - 1) AS StartTimeCol
     , generate_series(s.StartTime + 1, s.EndTime) AS EndTimeCol
FROM summaryTable s

В этой ссылке вы можете увидеть, как добавить функцию generate_series в SQL Server 2008.

Посмотреть в действии здесь

person atiruz    schedule 30.08.2012

arrow_upward
1
arrow_downward

Если вы используете SQL Server, вы можете попробовать следующий блок TSQL. Он использует курсор для обхода тех строк, время начала и окончания которых превышает 1 час, и выполняет итерацию, добавляя отдельные «часы» в переменную таблицы @gamesTable.

Когда курсор готов и заполняет записи в переменной @gamesTable, оператор SELECT для исходной таблицы для тех строк, у которых есть 1 или меньше часа между временем начала и окончания, объединяется со всеми строками, которые были сохранены в @gamesTable.

DECLARE Game_Cursor CURSOR FOR
    SELECT *
    FROM summaryTable
    WHERE EndTime - StartTime > 1

OPEN Game_Cursor;
DECLARE @PlayerID char
DECLARE @StartTime int
DECLARE @EndTime int
DECLARE @TempEndTime int
DECLARE @gamesTable TABLE(PlayerID char, StartTime int, EndTime int)

--Fetch the first row
FETCH NEXT FROM Game_Cursor INTO @PlayerID, @StartTime, @EndTime
WHILE @@FETCH_STATUS = 0
BEGIN
        WHILE (@EndTime - @StartTime) > 0
        BEGIN
            SET @TempEndTime = @StartTime + 1
            INSERT INTO @gamesTable
                SELECT @PlayerID AS PlayerID, @StartTime AS StartTime, @TempEndTime AS EndTime

            SET @StartTime = @StartTime + 1
        END;

        --Fetch the next row
        FETCH NEXT FROM Game_Cursor INTO @PlayerID, @StartTime, @EndTime
END;

--Rid of the cursor
CLOSE Game_Cursor;
DEALLOCATE Game_Cursor;

--Output the results
SELECT * FROM summaryTable WHERE (EndTime - StartTime) <= 1
UNION ALL
SELECT * FROM @gamesTable
person Nico M    schedule 30.08.2012
comment
Почему люди так ненавидят курсоры SQL? - person Kermit; 30.08.2012
comment
Я думаю, что некоторым людям не нравятся курсоры, потому что они иногда используются в ситуациях, когда они не являются наиболее эффективным решением и тратят ресурсы. - person Nico M; 05.09.2012

arrow_upward
1
arrow_downward

В SQL Server 2005+ вы можете использовать CTE для получения результатов:

;with cte (playerid, starttime, endtime) as 
(
  select playerid, min(starttime) starttime, max(endtime) endtime
  from yourtable
  group by playerid
  union all
  select playerid, starttime + 1, endtime
  from cte
  where starttime < endtime
)
select playerid,
  starttime,
  case 
    when starttime + 1 < endtime
    then starttime + 1
    else endtime
  end endtime
from cte
where starttime != endtime
order by playerid, starttime

См. SQL Fiddle с демонстрацией

person Taryn    schedule 07.09.2012