Gruppere og sortere efter datoer

Bare et skud, men hvad sker der, hvis sidste linje hedder:
" FROM Statistik GROUP BY Dato ORDER BY Dato DESC";

command.CommandText = "SELECT convert(nvarchar(10), Dato, 105) AS Dato,";
            command.CommandText += " COUNT(convert(nvarchar(10), Dato, 105)) AS AntalPerDag";
            command.CommandText += " FROM Statistik GROUP BY convert(nvarchar(10), Dato, 105) ORDER BY Dato DESC";

Det gør ingen forskel. Den tager vidst ikke ORDER BY sætningen med.

Det er faktisk lidt sjovt det her:

1: Du beder om at få sorteret efter  convert(nvarchar(10), Dato, 105), et udtryk der returnerer fx "13-02-2006". Med den sortering er det klart at "24-12-1947" kommer EFTER "23-10-2003", og det var ikke meningen, vel?

Det rigtige er altså at sortere efter Dato; ORDER BY Dato DESC, så får du den nyeste først.

MEN !!!!!!!1

2: I tabellen har du et felt der hedder Dato, det er datetime eller smalldatetime. Desuden har du i sætningen et felt du giver alias'et Dato (convert(nvarchar(10), Dato, 105) AS Dato). Nu er der altså TO ting i sætningen der hedder Dato, og når du så beder om at få sorteret, hvilken bestemmer MSSQL sig så for at bruge? Svar: Det felt du selv har navngivet Dato. Derfor får du samme resultat, det forkerte.

Løsningen er at undgå at bruge et eksisterende feltnavn so alias, jeg foreslår:

command.CommandText = "SELECT convert(nvarchar(10), Dato, 105) AS strDato,";
            command.CommandText += " COUNT(convert(nvarchar(10), Dato, 105)) AS AntalPerDag";
            command.CommandText += " FROM Statistik GROUP BY convert(nvarchar(10), Dato, 105) ORDER BY Dato DESC";

Jeg får så fejlen:

Column "Statistik.Dato" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

Tror forøvrigt jeg har prøvet noget lignende før, hvor jeg kaldte den Dato1 og serterede efter den i stedet for Dato.
Men nogenlunde samme resultat som det jeg fik med din statement :-(

Kan man ikke bruge SUBSTRING på et datetime felt?
Det ville jo gøre det en hel del nemmere.

Har forsøgt, men fik en fejl så måske kan det ikke lade sig gøre, eller også var jeg for klumret :-)

Det her virker:

command.CommandText = "SELECT LEFT(Dato, 11),";
            command.CommandText += " COUNT(LEFT(Dato, 11)) AS AntalPerDag";
            command.CommandText += " FROM Statistik GROUP BY LEFT(Dato, 11) ORDER BY LEFT(Dato, 11) DESC";

MEN, MEN, MEN...
Der sorteres desværre stadig ikke efter dato, men månederne er da i det mindste grupperet og dagene er talt op:

Column1    AntalPerDag
Oct 31 2005    3
Oct 29 2005    1
Oct 27 2005    1
Oct 26 2005    4
Oct 25 2005    1
Oct 24 2005    1
Oct 23 2005    1
Oct 22 2005    2
Oct 20 2005    1
Oct 18 2005    2
Oct 17 2005    2
Oct 15 2005    5
Oct 14 2005    4
Oct 13 2005    5
Oct 12 2005    5
Oct 11 2005    3
Oct 10 2005    3
Oct 9 2005    7
Oct 8 2005    5
Oct 7 2005    5
Oct 6 2005    3
Nov 29 2005    2
Nov 27 2005    1
Nov 24 2005    3
Nov 23 2005    2
Nov 22 2005    1
Nov 21 2005    4
Nov 20 2005    7
Nov 18 2005    1
Nov 17 2005    5
Nov 16 2005    1
Nov 15 2005    1
Nov 12 2005    1
Nov 11 2005    1
Nov 8 2005    2
Nov 3 2005    1
Jan 20 2006    2
Jan 19 2006    6
Jan 18 2006    2
Jan 17 2006    1
Jan 16 2006    2
Jan 14 2006    1
Jan 13 2006    1
Jan 12 2006    4
Jan 11 2006    3
Jan 10 2006    2
Jan 4 2006    1
Jan 3 2006    1
Feb 12 2006    71
Dec 30 2005    3
Dec 29 2005    2
Dec 24 2005    2
Dec 19 2005    1
Dec 18 2005    1
Dec 17 2005    3
Dec 16 2005    1
Dec 15 2005    2
Dec 14 2005    1
Dec 13 2005    2
Dec 11 2005    1
Dec 8 2005    2
Dec 6 2005    1
Dec 3 2005    1
Dec 2 2005    4

Du skal jo inkludere feltet i din GROUP BY, men så bliver det forkert, for alle datoerne er jo forskellige, fordi de har sekunder/millisekunder med Du vil have et tal for hver dag, ikke?

Så kan du bruge DATEDIFF:


SELECT convert(nvarchar(10), Max(Dato), 105) AS strDato,
  COUNT(Dato)) AS AntalPerDag
FROM Statistik
  GROUP BY DATEDIFF(d, Dato, GETDATE())
  ORDER BY DATEDIFF(d, Dato, GETDATE()) DESC 

Jeg grupperer altså efter "forskellen i hele dage mellem Dato og datoen i dag"; DATEDIFF(d, Dato, GETDATE())

Derfor kan jeg også sortere efter dettte.

Så kræver det et lille trick at SELECT'e Dato og der bruger jeg MAX. Der er jo en masse datoer i hver gruppe, men de er alle samme dato, blot (muligvis) forskellige klokkeslæt. Når de er fra samme dato er det ligegyldigt hvilken jeg viser, så jeg tager bare den største (hvilket vil sige den sidste). Jeg kunne lige så godt have taget den mindste, med MIN

Man kan jo kun selecte felter der includerede i GROUP BY eller som selectes med en aggregate function.

Det virker fint :-)
command.CommandText = "SELECT convert(nvarchar(10), Max(Dato), 105) AS strDato,";
            command.CommandText += " COUNT(Dato) AS AntalPerDag FROM Statistik";
            command.CommandText += " GROUP BY DATEDIFF(d, Dato, GETDATE())";
            command.CommandText += " ORDER BY DATEDIFF(d, Dato, GETDATE()) ASC";

Dog undrer det mig at jeg skal sortere ASC for at få den nyeste gruppe øverst. Hvorfor er det ikke DESC?

Det er også fint nok at det virker på denne måde, men kan det ikke laves mere enkelt (gennemskueligt), fx. med LEFT eller SUBSTRING?
Jeg har lidt svært ved at gennemskue 100% hvad det er der bliver gjort og vil gerne have en "nemmere" metode da jeg skal lave det med andre data i tabellen også.

Tak! Og på forhånd tak :-)

>> "Dog undrer det mig at jeg skal sortere ASC for at få den nyeste gruppe øverst. Hvorfor er det ikke DESC?"
Det er fordi der ordnes på forskellen mellem dagen i Dato og nu -> jo nyere dato, jo mindre forskel.

Ok. Det gir' mening.

ldanielsen, du skal jo have dine point :-) Du gav mig svaret på hvordan jeg kunne sortere og gruppere samtidig.

Med relation til mit sidste spørgsmål til dig. Er det helt umuligt at lave det med LEFT eller SUBSTRING?
Det vil i mine øjne lette mig for en del arbejde...

SURT

Jeg har lige skrevet en LAAAANGT indlæg om hvordan logikken er bag denne sætning, og så fumler jeg, og det er væk!!

Essensen er at du ikke skal bruge STRING funktioner på datoer, for det er for tungt og upraktisk. Du skal hellere blive fortrolig med de få datofunktioner der findes; DATEPART, DATEADD og DATEDIFF. Med de tre kan du lave alt det du kan ønske dig.

Ang. gruppering skal man vænne sig til at bruge de forskellige Aggregate Functions der er, mest bruges: COUNT, SUM, MAX, MIN og AVG. Det er funktioner der kan samle et sæt af data og returnere én værdi:

COUNT - Antal værdier i sættet
SUM - Summen af sættets værdier
MAX - den højeste værdi i sættet
MIN - den mindste værdi i sættet
AVG - gennemsnittet af værdier i sættet

Tak for hjælpen :-)

Du burde nok overveje at omstrukturere din tabelstruktur. At konvertere data på den måde tvinger du serveren til at lave et table scan hver gang.

jnd > Hvad mener du?

Hvis du konverterer data med eksempelvis CAST eller CONVERT (eller en skalar funktion som DATEPART) så arbejder serveren på output data fra dise, og ikke tabellen, og demed heller ikke de index der måtte være, eksempelvis:

SELECT CAST(col1 AS VARCHAR(10))
FROM tab1
GROUP BY CAST(col1 AS VARCHAR(10))

Hvis du nu havde et index defineret på col1 kan dette ikke bruges, da du jo ikke arbejder på de data der er i indexet, derfor vil dit query bruge et table scan i stedet. Man bør derfor overveje om ikke det var bedre at omstrukturere sine tabeller så de svarer bedre til de data man har brug for.

Befinder du dig på SQL server 2005, er der en datatype der "kun" indeholder dato eller tids komponenten i forvejen. På SQL server 2000, må du selv arbejde lave det ved at bruge en int til tidskomponenten og en int til dato komponenten (eller smallint hvis du ikke har brug for en særlig høj opløsning)

"Befinder du dig på SQL server 2005, er der en datatype der "kun" indeholder dato eller tids komponenten i forvejen."

Ja, den er også blevet overvejet, men da statistikken skal verificere om en ip har været talt op indenfor de sidste 20 minutter er det jo udelukket.
Men ellers har du da fuldstændig ret Ind!

jnd > Jeg har testet lidt på det du siger, og jeg må sige, at hvis jeg indexerer mine datofelter, så kan jeg ikke få den til at lave en table scan, uanset om jeg bruger CONVERT eller DATEDIFF, om jeg sorterer eller grupperer eller hvad. Den arbejder udelukkende på index'et, som jeg også forventede det.

Jeg tror du tager fejl.

ldanielsen, bare sådan af ren og skær interesse (er selv lige startet med at bruger sql express edition). Hvordan laver du sådanne tests?

Jeg ved ikke hvilke redskaber du har med den udgave, men jeg bruger Query Analyzer.

Skriv din query i Query Analyzer, og vælg "Show Execution Plan" (Enten i Execute Mode dropdown'en eller i menu'en Query)

Kør Query'en, og skift fanen nederst i result-vinduet til "Execution Plan".

Jeg har ikke frygtelig meget forstand på at tyde dem, men alligevel kan det være med til at finde "flaskehalsene" i en query, og en Table Scan kan man da altid få øje på :o)

Ok. Tak

Beklager det lidt sene svar. Men ldanielsen, du må læse din execution plan forkert. Det kan ganske enkelt ikke lade sig gøre.

Hvis du kører convert på en søjle i tabellen arbejder du udelukkende på resultatet, der genereres INTET metadata der fortæller hvor stammer fra, derfor kan der på ingen måde anvendes et index. Convert skifter format på data, det er ikke blot et "filter" der præsenterer data på en ny måde.

Jeg har ikke lige adgang til en af mine servere nu, så koden skal måske lige have rettet et par syntax fejl, men prøv følgende eksempel. Af de to select statements i bunden vil kun det ene anvente indexet på den primære nøgle.

CREATE TABLE #T( col1 int PRIMARY KEY CLUSTERED, col2 char(100) )

DECLARE @i int
SELECT @i = 0

WHILE @i > 1000000
BEGIN

INSERT INTO #T(col1,col2)
VAULE (@i,'some string')

SELECT @i = @i + 1

END

SELECT col1,col2
FROM #T
WHERE col1 = 500000

SELECT col1,col2
FROM #T
WHERE CAST(@col1 AS VARCHAR(10)) = '500000'

Nu skal vi måske ikke bruge thomasso's spørgsmål til denne diskussion, men jeg vil faktisk gerne vide hvad det er du mener.

Jeg har omskrevet dit eks, rettet et par fejl, og laver en fysisk tabel i stedet for din temp tabel.

Og så laver den ikke noget table scan, men et index scan, hvis jeg bruger CAST, men blot et indes seek hvis jeg ikke gør. Jeg bestrider ikke at det går ud over performance, jeg plæderer netop for ikke at bruge CONVERT/CAST i group by, order by og where clauses. Jeg anbefaler i dette tilfælde en DATEDIFF eller DATEPART, uden at vide det bestemt tror jeg det må være en fordel.

Men det er forkert at indexer ignoreres i disse tilfælde. Laver din server et Table Scan??:


CREATE TABLE T( col1 int PRIMARY KEY CLUSTERED, col2 char(100) )

DECLARE @i int
SELECT @i = 1

WHILE @i <= 1000000
BEGIN

INSERT INTO T(col1,col2)
VALUES (@i, 'some string')

SELECT @i = @i + 1

END

SELECT col1,col2
FROM T
WHERE col1 = 500000

SELECT col1,col2
FROM T
WHERE CAST(col1 AS VARCHAR(10)) = '500000'

Ahh, jeg tror jeg ved hvad problemet er.

Du ser et Index scan i din query analyse. I dette tilfælde er det faktisk det samme som et table scan (da det er et clustered index). Et index scan anvender ikke indexet på en hensigtsmæssig måde.

Læg mærke til at der i execution planen for :

SELECT col1,col2
FROM T
WHERE col1 = 500000

Står index SEEK, og ikke scan. Omvendt står der Index scan for:

SELECT col1,col2
FROM T
WHERE CAST(col1 AS VARCHAR(10)) = '500000'

Forskellen ligger i at man i et index scan skal læser hele leaf niveauet igennem, mens et index seek laver et opslag i indexet. Det kan godt være at det lyder som en akademisk forskel, men query 2 skal faktisk lave ca 60 gange mere arbejde end query 1