Jak pogrupować czas według godziny lub 10 minut

jak kiedy to robię

SELECT [Date]
  FROM [FRIIB].[dbo].[ArchiveAnalog]
  GROUP BY [Date]

jak mogę określić okres dla grupy?

MS SQL 2008

2nd Edit

Próbuję

SELECT MIN([Date]) AS RecT, AVG(Value)
  FROM [FRIIB].[dbo].[ArchiveAnalog]
  GROUP BY (DATEPART(MINUTE, [Date]) / 10)
  ORDER BY RecT

zmieniono% 10 na / 10. czy można wyprowadzić datę bez milisekund?

w końcu skończone

GROUP BY
DATEPART(YEAR, DT.[Date]),
DATEPART(MONTH, DT.[Date]),
DATEPART(DAY, DT.[Date]),
DATEPART(HOUR, DT.[Date]),
(DATEPART(MINUTE, DT.[Date]) / 10)

Jestem bardzo spóźniony na imprezę, ale to nie pojawia się w żadnej z istniejących odpowiedzi:

GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, '2000', date_column) / 10 * 10, '2000')

• Te 10i MINUTEterminy mogą zostać zmienione na dowolną liczbę iDATEPART odpowiednio.
• To jest DATETIMEwartość, która oznacza:
  • Działa dobrze w długich odstępach czasu. (Nie ma kolizji między latami.)
  • Włączenie go do SELECTinstrukcji da wynikowi kolumnę z ładnymi danymi wyjściowymi obciętymi na określonym poziomie.
• '2000'to „data zakotwiczenia”, wokół której SQL wykona obliczenia związane z datą. Jereonh odkrył poniżej, że przy poprzedniej funkcji anchor ( 0) napotykasz przepełnienie liczb całkowitych, gdy grupujesz ostatnie daty według sekund lub milisekund. ^†

SELECT   DATEADD(MINUTE, DATEDIFF(MINUTE, '2000', aa.[date]) / 10 * 10, '2000')
                                                               AS [date_truncated],
         COUNT(*) AS [records_in_interval],
         AVG(aa.[value]) AS [average_value]
FROM     [friib].[dbo].[archive_analog] AS aa
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, '2000', aa.[date]) / 10 * 10, '2000')
ORDER BY [date_truncated]

Jeśli Twoje dane obejmują stulecia, ^‡ używanie pojedynczej daty zakotwiczenia dla grupowania w sekundach lub milisekundach nadal spowoduje przepełnienie. Jeśli tak się dzieje, możesz poprosić każdy wiersz o zakotwiczenie porównania kategoryzacji do północy jego własnej daty:

• Użyj DATEADD(DAY, DATEDIFF(DAY, 0, aa.[date]), 0)zamiast tego, '2000'gdzie pojawia się powyżej. Twoje zapytanie będzie całkowicie nieczytelne, ale zadziała.

• Alternatywą może być CONVERT(DATETIME, CONVERT(DATE, aa.[date]))zamiana.

_{^† 2 ³² ≈ 4,29E + 9, więc jeśli tak DATEPARTjest SECOND, zyskujesz 4,3 miliarda sekund w każdą stronę, czyli „kotwicę ± 136 lat”. Podobnie, 2 ³² milisekundy to ≈ 49,7 dnia.
^‡ Jeśli Twoje dane obejmują stulecia lub milenia i nadal są dokładne z dokładnością do sekundy lub milisekundy… gratulacje! Cokolwiek robisz, rób to dalej.}

W T-SQL możesz:

SELECT [Date]
  FROM [FRIIB].[dbo].[ArchiveAnalog]
  GROUP BY [Date], DATEPART(hh, [Date])

lub

na minutę użytkowania DATEPART(mi, [Date])

lub

po 10 minutach użycia DATEPART(mi, [Date]) / 10(jak zasugerował Timothy)

Przez 10 minut zrobiłbyś to

GROUP BY (DATEPART(MINUTE, [Date]) / 10)

Jak już wspominali tzup i Pieter888 ... po prostu zrobić godzinną przerwę

GROUP BY DATEPART(HOUR, [Date])

Powinno być coś takiego

select timeslot, count(*)  
from 
    (
    select datepart('hh', date) timeslot
    FROM [FRIIB].[dbo].[ArchiveAnalog]  
    ) 
group by timeslot

(Nie jestem w 100% pewien składni - jestem bardziej typem z Oracle)

W Oracle:

SELECT timeslot, COUNT(*) 
FROM
(  
    SELECT to_char(l_time, 'YYYY-MM-DD hh24') timeslot 
    FROM
    (
        SELECT l_time FROM mytab  
    )  
) GROUP BY timeslot 

Oryginalna odpowiedź, której udzielił autor, działa całkiem nieźle. Aby rozszerzyć ten pomysł, możesz zrobić coś takiego

group by datediff(minute, 0, [Date])/10

co pozwoli ci na grupowanie w dłuższym okresie niż 60 minut, powiedzmy 720, czyli pół dnia itd.

Dla MySql:

GROUP BY
DATE(`your_date_field`),
HOUR(`your_date_field`),
FLOOR( MINUTE(`your_date_field`) / 10);

Moim rozwiązaniem jest użycie funkcji do utworzenia tabeli z przedziałami dat, a następnie dołączenie tej tabeli do danych, które chcę zgrupować, używając przedziału dat w tabeli. Przedział dat można wtedy łatwo wybrać podczas prezentacji danych.

CREATE FUNCTION [dbo].[fn_MinuteIntervals]
    (
      @startDate SMALLDATETIME ,
      @endDate SMALLDATETIME ,
      @interval INT = 1
    )
RETURNS @returnDates TABLE
    (
      [date] SMALLDATETIME PRIMARY KEY NOT NULL
    )
AS
    BEGIN
        DECLARE @counter SMALLDATETIME
        SET @counter = @startDate
        WHILE @counter <= @endDate
            BEGIN
                INSERT INTO @returnDates VALUES ( @counter )
                SET @counter = DATEADD(n, @interval, @counter)
            END
        RETURN
    END

declare @interval tinyint
set @interval = 30
select dateadd(minute,(datediff(minute,0,[DateInsert])/@interval)*@interval,0), sum(Value_Transaction)
from Transactions
group by dateadd(minute,(datediff(minute,0,[DateInsert])/@interval)*@interval,0)

W przypadku programu SQL Server 2012, chociaż uważam, że będzie działać w programie SQL Server 2008R2, używam następującego podejścia, aby skrócić czas do milisekundy:

DATEADD(MILLISECOND, -DATEDIFF(MILLISECOND, CAST(time AS DATE), time) % @msPerSlice, time)

Działa to poprzez:

• Uzyskanie liczby milisekund między ustalonym punktem a czasem docelowym:
  @ms = DATEDIFF(MILLISECOND, CAST(time AS DATE), time)
• Biorąc resztę z podzielenia tych milisekund na wycinki czasu:
  @rms = @ms % @msPerSlice
• Dodając ujemną pozostałość do czasu docelowego, aby uzyskać czas wycinka:
  DATEADD(MILLISECOND, -@rms, time)

Niestety, ponieważ przepełnia to mikrosekundami i mniejszymi jednostkami, więc większe, dokładniejsze zestawy danych wymagałyby użycia mniej wygodnego stałego punktu.

Nie przeprowadziłem rygorystycznych testów porównawczych i nie jestem w dużych danych, więc Twój przebieg może się różnić, ale wydajność nie była zauważalnie gorsza niż inne metody wypróbowane na naszym sprzęcie i zestawach danych, a wypłata w wygodzie programisty za dowolne cięcie sprawia, że ​​warto dla nas.

select from_unixtime( 600 * ( unix_timestamp( [Date] ) % 600 ) ) AS RecT, avg(Value)
from [FRIIB].[dbo].[ArchiveAnalog]
group by RecT
order by RecT;

zamień dwie 600 na dowolną liczbę sekund, którą chcesz zgrupować.

Jeśli potrzebujesz tego często, a tabela się nie zmienia, jak sugeruje nazwa Archive, prawdopodobnie byłoby nieco szybciej przekonwertować i zapisać datę (i czas) jako unikatowy czas w tabeli.

Wiem, że spóźniłem się na występ z tym, ale zastosowałem to - całkiem proste podejście. Pozwala to uzyskać 60-minutowe plasterki bez problemów z zaokrąglaniem.

Select 
   CONCAT( 
            Format(endtime,'yyyy-MM-dd_HH:'),  
            LEFT(Format(endtime,'mm'),1),
            '0' 
          ) as [Time-Slice]

select dateadd(minute, datediff(minute, 0, Date), 0),
       sum(SnapShotValue)
FROM [FRIIB].[dbo].[ArchiveAnalog]
group by dateadd(minute, datediff(minute, 0, Date), 0)