Группировка T-SQL по нестандартным временным рамкам

У меня есть оператор SQL, который отлично работает, если мне нужны мои SUMmations для каждого часа (SQL Server 2008). DATEPART(HOUR, DATE_TIME) делает за меня всю замечательную работу.

SELECT  SUM(case STATION_ID when 'S-WELDCHK' then 1 else 0 end) as WELDCHK
       ,SUM(case STATION_ID when 'S-GLUING-OUT-OK' then 1 else 0 end) as GLUING
       ,SUM(case STATION_ID when 'S-GLUING-OUT-NOK' then 1 else 0 end) as 'GLUING-NOK'
       ,SUM(case STATION_ID when 'S-ULTRAWELD-OUT-OK' then 1 else 0 end) as ULTRAWELD
       ,SUM(case STATION_ID when 'S-ULTRAWELD-OUT-NOK' then 1 else 0 end) as 'ULTRAWELD-NOK'
       ,SUM(case STATION_ID when 'S-BOLTFAST-OUT-OK' then 1 else 0 end) as BOLTFAST
       ,SUM(case STATION_ID when 'S-BOLTFAST-OUT-NOK' then 1 else 0 end) as 'BOLTFAST-NOK'
       ,SUM(case STATION_ID when 'S-MAPVISION-OUT-OK' then 1 else 0 end) as MAPVISION
       ,SUM(case STATION_ID when 'S-MAPVISION-OUT-NOK' then 1 else 0 end) as 'MAPVISION-NOK'
       ,SUM(case STATION_ID when 'S-CHECKFIX-OUT-OK' then 1 else 0 end) as CHECKFIX
       ,SUM(case STATION_ID when 'S-CHECKFIX-OUT-NOK' then 1 else 0 end) as 'CHECKFIX-NOK'
       ,SUM(case STATION_ID when 'S-EJOT-OUT-OK' then 1 else 0 end) as EJOT
       ,SUM(case STATION_ID when 'S-EJOT-OUT-NOK' then 1 else 0 end) as 'EJOT-NOK'
  FROM [dbFactory].[dbo].[Events]
  where (DATEPART(yy,DATE_TIME) = 2014
         AND DATEPART(mm,DATE_TIME) = 2
         AND DATEPART(dd,DATE_TIME)= 5)
  GROUP BY 
        DATEPART(HOUR, DATE_TIME)
 with rollup

На самом деле я хочу SUMS для нерегулярных периодов времени, которые у меня есть во временной таблице (усечено для краткости)

Start         Finish
06:00:00.000  06:30:00.000
06:30:00.000  07:30:00.000
07:30:00.000  08:30:00.000
08:30:00.000  09:30:00.000
09:30:00.000  10:00:00.000
10:00:00.000  10:30:00.000
10:30:00.000  11:30:00.000
11:30:00.000  12:30:00.000
12:30:00.000  13:30:00.000
13:30:00.000  14:00:00.000

Любые предложения на веб-сайтах или то, что я должен прочитать, чтобы решить эту проблему. Предполагается, что должно быть средство для свертки пользовательской функции, которую, возможно, я мог бы передать периодам START и FINISH и DATE_TIME транзакции.


sql-server rollup group-by
person kingchris    schedule 25.02.2014    source источник
comment
извините, не ясно. где образец данных нерегулярных периодов времени. почему три отдельных пункта where вместо одного datetime.   -  person KumarHarsh    schedule 25.02.2014
comment
Три отдельные причины для даты и времени - это ошибка с моей стороны, просто чтобы заставить ее работать. Примеры данных за нерегулярные периоды времени находятся во временной таблице с именами полей «Начало» и «Окончание».   -  person kingchris    schedule 25.02.2014
comment
если входной временной интервал равен 30 мин. то вы можете запросить как datediff(минута,начало,конец)›30   -  person KumarHarsh    schedule 25.02.2014
comment
Правильный. К сожалению, у меня есть серия получасовых и часовых периодов времени, которые перекрываются с часами.   -  person kingchris    schedule 25.02.2014

Ответы (2)


arrow_upward
1
arrow_downward

Присоедините свой запрос к таблице Periods, используя between

     inner join Periods on events.DATE_TIME between Periods.Start and Periods.End

и сгруппировать по полю в таблице «Периоды»

     group by Periods.Start

NB. Вам нужно решить, что произойдет, если период времени находится точно на границе между двумя периодами. Возможно, вам придется настроить периоды или соединить с помощью > и ‹=

Вы также можете посмотреть на PIVOT, а не использовать конструкцию SUM(CASE....

person podiluska    schedule 25.02.2014
comment
Спасибо. Я посмотрю на это. - person kingchris; 25.02.2014

arrow_upward
0
arrow_downward

Вот как я получил рабочее решение. Это не красиво и не эффективно. Это часть отчета, который в настоящее время запускается каждые 24 часа.

Хранимая процедура, которая получает данные

USE [dbFactory]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 20140225
-- Description: Used by a report to extract SUM values for various Factory stations
-- =============================================
ALTER PROCEDURE [dbo].[spRptFetchStationActivitySummationsWithGroupBy] 
    @Day DATETIME
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @START DATETIME;
    DECLARE @FINISH DATETIME;

    if @Day IS NULL
    BEGIN
        SET @DAY = GETDATE();
    END
    -- Subtract a day from the day passed in and set its time to 6am 
    DECLARE @ActualDay DATETIME = DATEADD(day,-1,DATEADD(HOUR,6,DATEADD(dd, 0, DATEDIFF(dd, 0, @DAY))));
    DECLARE @dtTwentyFoursHoursLater DATETIME = DATEADD(HOUR,24,@ActualDay);

    -- If a transaction time is exactly on 6:00 am we want to avoid counting it twice.
    -- There are granularity issues on milliseconds
    SET @dtTwentyFoursHoursLater = DATEADD(millisecond,-2,@dtTwentyFoursHoursLater);
    -- SELECT @ActualDay,@dtTwentyFoursHoursLater

    CREATE TABLE #LocalEvents(
        [SERIAL_NUMBER] [nchar](20) NOT NULL,
        [DATE_TIME] [datetime] NOT NULL,
        [STATION_ID] [nchar](20) NOT NULL,
        [SUCCESS] [char](1) NOT NULL,
        [OVERRIDDEN] [char](1) NOT NULL,
        [USER_NAME] [nchar](30) NOT NULL,
        [EVENT_ID] [smallint] NOT NULL,
        [EXTRA_INFO] [nchar](30) NOT NULL);

    -- Stuff in the dummy entries which have one entry for each time frame from for the GROUP BY
    INSERT INTO #LocalEvents([SERIAL_NUMBER],
                             [DATE_TIME],
                             [STATION_ID],
                             [SUCCESS],
                             [OVERRIDDEN],
                             [USER_NAME],
                             [EVENT_ID],
                             [EXTRA_INFO]
                           )    
        SELECT               [SERIAL_NUMBER],
                             [DATE_TIME],
                             [STATION_ID],
                             [SUCCESS],
                             [OVERRIDDEN],
                             [USER_NAME],
                             [EVENT_ID],
                             [EXTRA_INFO]

        FROM dbCCB.dbo.Events;                         

    -- Stuff in the real events of the past 24 hours
    INSERT INTO #LocalEvents([SERIAL_NUMBER],
                             [DATE_TIME],
                             [STATION_ID],
                             [SUCCESS],
                             [OVERRIDDEN],
                             [USER_NAME],
                             [EVENT_ID],
                             [EXTRA_INFO]
                           )    
        SELECT [SERIAL_NUMBER],
                             [DATE_TIME],
                             [STATION_ID],
                             [SUCCESS],
                             [OVERRIDDEN],
                             [USER_NAME],
                             [EVENT_ID],
                             [EXTRA_INFO]

        FROM dbFactory.dbo.Events
        WHERE DATE_TIME BETWEEN @ActualDay AND @dtTwentyFoursHoursLater;


    -- The UDF returns the first two chars to show the order as is 01 to 30 for the time periods.
    -- We chop them off (SUBSTRING) to provide the client with the start and end times of the time period.
    SELECT  SUBSTRING(dbo.fnReturnGroupWhenBetweenTimes( CONVERT(TIME,DATE_TIME)),4,200) as [Time] 
           ,SUM(case STATION_ID when 'S-WELDCHK' then 1 else 0 end) as WELDCHK
           ,SUM(case STATION_ID when 'S-GLUING-OUT-OK' then 1 else 0 end) as GLUING
           ,SUM(case STATION_ID when 'S-GLUING-OUT-NOK' then 1 else 0 end) as 'GLUING-NOK'
           ,SUM(case STATION_ID when 'S-ULTRAWELD-OUT-OK' then 1 else 0 end) as ULTRAWELD
           ,SUM(case STATION_ID when 'S-ULTRAWELD-OUT-NOK' then 1 else 0 end) as 'ULTRAWELD-NOK'
           ,SUM(case STATION_ID when 'S-BOLTFAST-OUT-OK' then 1 else 0 end) as BOLTFAST
           ,SUM(case STATION_ID when 'S-BOLTFAST-OUT-NOK' then 1 else 0 end) as 'BOLTFAST-NOK'
           ,SUM(case STATION_ID when 'S-MAPVISION-OUT-OK' then 1 else 0 end) as MAPVISION
           ,SUM(case STATION_ID when 'S-MAPVISION-OUT-NOK' then 1 else 0 end) as 'MAPVISION-NOK'
           ,SUM(case STATION_ID when 'S-CHECKFIX-OUT-OK' then 1 else 0 end) as CHECKFIX
           ,SUM(case STATION_ID when 'S-CHECKFIX-OUT-NOK' then 1 else 0 end) as 'CHECKFIX-NOK'
           ,SUM(case STATION_ID when 'S-EJOT-OUT-OK' then 1 else 0 end) as EJOT
           ,SUM(case STATION_ID when 'S-EJOT-OUT-NOK' then 1 else 0 end) as 'EJOT-NOK'
           /*,SUM(case STATION_ID when 'S-GENERIC' then 1 else 0 end) as 'GENERIC'*/
      FROM #LocalEvents
      GROUP BY dbo.fnReturnGroupWhenBetweenTimes(CONVERT(TIME,DATE_TIME))
      WITH ROLLUP

      DROP TABLE #LocalEvents;
END

Пользовательская функция, фильтрующая события в нерегулярные смены.

USE [dbFactory]
GO
/****** Object:  UserDefinedFunction [dbo].[fnReturnGroupWhenBetweenTimes]    Script Date: 03/14/2014 12:44:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 20140226
-- Description: Returns a 1 if Date's TIME is between two dates
-- =============================================
ALTER FUNCTION [dbo].[fnReturnGroupWhenBetweenTimes] 
(
    -- Add the parameters for the function here
    @TestTime TIME
)
RETURNS VARCHAR(20)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result VARCHAR(20)

    -- Add the T-SQL statements to compute the return value here
    SELECT @Result = 
        case
            when @TestTime between CONVERT(TIME,'06:00',14) and CONVERT(TIME,'06:30',14) then '01 06:00 06:30'
            when @TestTime between CONVERT(TIME,'06:30',14) and CONVERT(TIME,'07:30',14) then '02 06:30 07:30'
            when @TestTime between CONVERT(TIME,'07:30',14) and CONVERT(TIME,'08:30',14) then '03 07:30 08:30'
            when @TestTime between CONVERT(TIME,'08:30',14) and CONVERT(TIME,'09:30',14) then '04 08:30 09:30'
            when @TestTime between CONVERT(TIME,'09:30',14) and CONVERT(TIME,'10:00',14) then '05 09:30 10:00'
            when @TestTime between CONVERT(TIME,'10:00',14) and CONVERT(TIME,'10:30',14) then '06 10:00 10:30'
            when @TestTime between CONVERT(TIME,'10:30',14) and CONVERT(TIME,'11:30',14) then '07 10:30 11:30'
            when @TestTime between CONVERT(TIME,'11:30',14) and CONVERT(TIME,'12:30',14) then '08 11:30 12:30' 
            when @TestTime between CONVERT(TIME,'12:30',14) and CONVERT(TIME,'13:30',14) then '09 12:30 13:30' 
            when @TestTime between CONVERT(TIME,'13:30',14) and CONVERT(TIME,'14:00',14) then '10 13:30 14:00'

            when @TestTime between CONVERT(TIME,'14:00',14) and CONVERT(TIME,'14:30',14) then '11 14:00 14:30'
            when @TestTime between CONVERT(TIME,'14:30',14) and CONVERT(TIME,'15:30',14) then '12 14:30 15:30'
            when @TestTime between CONVERT(TIME,'15:30',14) and CONVERT(TIME,'16:30',14) then '13 15:30 16:30'
            when @TestTime between CONVERT(TIME,'16:30',14) and CONVERT(TIME,'17:30',14) then '14 16:30 17:30'
            when @TestTime between CONVERT(TIME,'17:30',14) and CONVERT(TIME,'18:00',14) then '15 17:30 18:00'
            when @TestTime between CONVERT(TIME,'18:00',14) and CONVERT(TIME,'18:30',14) then '16 18:00 18:30'
            when @TestTime between CONVERT(TIME,'18:30',14) and CONVERT(TIME,'19:30',14) then '17 18:30 19:30'
            when @TestTime between CONVERT(TIME,'19:30',14) and CONVERT(TIME,'20:30',14) then '18 19:30 20:30'
            when @TestTime between CONVERT(TIME,'20:30',14) and CONVERT(TIME,'21:30',14) then '19 20:30 21:30'
            when @TestTime between CONVERT(TIME,'21:30',14) and CONVERT(TIME,'22:00',14) then '20 21:30 22:00'

            when @TestTime between CONVERT(TIME,'22:00',14) and CONVERT(TIME,'22:30',14) then '21 22:00 22:30'
            when @TestTime between CONVERT(TIME,'22:30',14) and CONVERT(TIME,'23:30',14) then '22 22:30 23:30'

            when @TestTime between CONVERT(TIME,'23:30',14) and CONVERT(TIME,'23:59:59.998',14) then '23 23:30 00:30'
            when @TestTime between CONVERT(TIME,'00:00',14) and CONVERT(TIME,'00:30',14) then '23 23:30 00:30'

            when @TestTime between CONVERT(TIME,'00:30',14) and CONVERT(TIME,'01:30',14) then '24 00:30 01:30'
            when @TestTime between CONVERT(TIME,'01:30',14) and CONVERT(TIME,'02:00',14) then '25 01:30 02:00'
            when @TestTime between CONVERT(TIME,'02:00',14) and CONVERT(TIME,'02:30',14) then '26 02:00 02:30' 
            when @TestTime between CONVERT(TIME,'02:30',14) and CONVERT(TIME,'03:30',14) then '27 02:30 03:30'
            when @TestTime between CONVERT(TIME,'03:30',14) and CONVERT(TIME,'04:30',14) then '28 03:30 04:30'
            when @TestTime between CONVERT(TIME,'04:30',14) and CONVERT(TIME,'05:30',14) then '29 04:30 05:30'
            when @TestTime between CONVERT(TIME,'05:30',14) and CONVERT(TIME,'06:00',14) then '30 05:30 06:00'

            else 'UnKnown'
        end

    -- Return the result of the function
    RETURN @Result

END

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

SERIAL_NUMBER           DATE_TIME
DUMMY-SERIAL            2014-03-14 00:00:00.000
DUMMY-SERIAL            2014-03-14 01:00:00.000
DUMMY-SERIAL            2014-03-14 01:45:00.000
DUMMY-SERIAL            2014-03-14 02:15:00.000
DUMMY-SERIAL            2014-03-14 03:00:00.000
DUMMY-SERIAL            2014-03-14 04:00:00.000
DUMMY-SERIAL            2014-03-14 05:00:00.000
DUMMY-SERIAL            2014-03-14 05:45:00.000
DUMMY-SERIAL            2014-03-14 06:15:00.000
DUMMY-SERIAL            2014-03-14 07:00:00.000
DUMMY-SERIAL            2014-03-14 08:00:00.000
DUMMY-SERIAL            2014-03-14 09:00:00.000
DUMMY-SERIAL            2014-03-14 09:45:00.000
DUMMY-SERIAL            2014-03-14 10:15:00.000
DUMMY-SERIAL            2014-03-14 11:00:00.000
DUMMY-SERIAL            2014-03-14 12:00:00.000
DUMMY-SERIAL            2014-03-14 13:00:00.000
DUMMY-SERIAL            2014-03-14 13:45:00.000
DUMMY-SERIAL            2014-03-14 14:15:00.000
DUMMY-SERIAL            2014-03-14 15:00:00.000
DUMMY-SERIAL            2014-03-14 16:00:00.000
DUMMY-SERIAL            2014-03-14 17:00:00.000
DUMMY-SERIAL            2014-03-14 17:45:00.000
DUMMY-SERIAL            2014-03-14 18:15:00.000
DUMMY-SERIAL            2014-03-14 19:00:00.000
DUMMY-SERIAL            2014-03-14 20:00:00.000
DUMMY-SERIAL            2014-03-14 21:00:00.000
DUMMY-SERIAL            2014-03-14 21:45:00.000
DUMMY-SERIAL            2014-03-14 22:15:00.000
DUMMY-SERIAL            2014-03-14 23:00:00.000

Я благодарю многих людей из StackOverflow за помощь. Я прочитал много вопросов и ответов. Теперь мне нужно узнать о FIRST_VALUE и LAST_VALUE в моих группах, но заставить их работать в 2008, а не в 2012 году. Это никогда не закончится.

person kingchris    schedule 14.03.2014