Wie kann ich erstellen Sie eine Reihe von Monaten beitreten spärliche Daten zu?

Ich denke, das ist eine ziemlich häufige Frage, aber ich weiß nicht, was der Prozess genannt, also werde ich beschreiben es mit einem Beispiel. Das Konzept ist, dass ich beitreten soll eine sparse-dataset, um eine komplette Serie, wie die Tage der Woche, Monate des Jahres, oder jeden bestellten set (Z. B. für das ranking). Leere Positionen in den spärlichen Daten, die zeigen, wie NULL-neben der kompletten Serie.

Sagen wir, ich führen Sie die folgende Abfrage in SQL-Servers, um herauszufinden, die monatlichen Verkäufe.

SELECT
    YEAR([timestamp]),
    MONTH([timestamp]),
    COUNT(*)
FROM table1
WHERE YEAR([timestamp]) = YEAR(GETDATE())
GROUP BY
    YEAR([timestamp]),
    MONTH([timestamp])
ORDER BY
    YEAR([timestamp]) DESC,
    MONTH([timestamp]) DESC;

Wenn jedoch Umsatz nur traten im Mai und August dieses Jahres, zum Beispiel, dann die return-Ergebnis würde dann so Aussehen:

2010    August    1234
2010    May       5678

Möchte ich mein Ergebnis zurückgeben set wie folgt Aussehen:

2010    January
2010    February
2010    March
2010    April
2010    May        1234
2010    June
2010    July
2010    August     5678
2010    September
2010    October
2010    November
2010    December

Ich weiß nur einen Weg, dies zu tun, ist dies:

SELECT
    YEAR(GETDATE()),
    month_index.month_name,
    sales_data.sales
FROM (
    SELECT 'January' as month_name, 1 as month_number
    UNION
    SELECT 'February', 2
    UNION
    SELECT 'March', 3
    UNION
    SELECT 'April', 4
    UNION
    SELECT 'May', 5
    UNION
    SELECT 'June', 6
    UNION
    SELECT 'July', 7
    UNION
    SELECT 'August', 8
    UNION
    SELECT 'September', 9
    UNION
    SELECT 'October', 10
    UNION
    SELECT 'November', 11
    UNION
    SELECT 'December', 12
) as month_index
LEFT JOIN (
    SELECT
        YEAR([timestamp]) AS year_name,
        MONTH([timestamp]) AS month_name,
        COUNT(*) AS sales
    FROM table1
    WHERE YEAR([timestamp]) = GETDATE()
    GROUP BY
        YEAR([timestamp]),
        MONTH([timestamp])
) AS sales_data
ON month_index.month_name = sales_data.month_name
ORDER BY
    month_index.month_number DESC;

Gibt es einen besseren Weg, um erstellen Sie das vollständige Datum und die alphanumerische Serie beizutreten Daten auf? Und was ist das genannt?

Dank!

Verwandte: stackoverflow.com/questions/1478951/...

InformationsquelleAutor Kuyenda | 2010-06-25

probieren Sie etwas wie dieses:

DECLARE @StartDate datetime
       ,@EndDate datetime
SELECT @StartDate=DATEADD(month,-6,DATEADD(month,DATEDIFF(month,0,GETDATE()),0) )
      ,@EndDate=GETDATE()

;with AllDates AS
(
    SELECT @StartDate AS DateOf
    UNION ALL
    SELECT DateAdd(month,1,DateOf)
        FROM AllDates
    WHERE DateOf<@EndDate
)
SELECT * FROM AllDates

Ausgabe:

DateOf
-----------------------
2009-12-01 00:00:00.000
2010-01-01 00:00:00.000
2010-02-01 00:00:00.000
2010-03-01 00:00:00.000
2010-04-01 00:00:00.000
2010-05-01 00:00:00.000
2010-06-01 00:00:00.000
2010-07-01 00:00:00.000

(8 row(s) affected)

Durch den rekursiven Ansatz, der OPTION (MAXRECURSION 0) oder OPTION (MAXRECURSION 1000) werden kann, die für große Datenbereiche.
danke, ja es würde
auf der anderen Seite (wie ich gerade gelernt), für große Reichweiten, rekursive zählen Strategien sind wahrscheinlich nicht die beste Wahl, jedenfalls laut diesem Artikel: Versteckte RBAR: Zählen mit Rekursiven Allgemeinen Tabellenausdrucks ' s
ja, das ist wahr, versuchen Sie, eine zahlen-Tabelle: stackoverflow.com/q/1393951/65223

InformationsquelleAutor KM.

3

Anfragen wie diese sind einer der Hauptgründe, dass viele erfahrene Datenbankadministratoren (DBAs) oder Datenbank-Programmierer halten Kalender Tisch in Ihren Datenbanken.
- +1 für den link zu Kalender-Tabelle.
- Ich habe auch ständig eine Tabelle von a bis z und 0 bis 999999. Das war für PHP. Danke!
- Eine Kalender - /Datum - /zahlen-Tabelle ist die einzige Möglichkeit, dies zu tun, auf SQL Server 2000 oder älter. Für SQL Server 2005+, ein rekursiver CTE ist effizienter.
- Der rekursive CTE ist nett, aber die 100 loop cap ist ein Mist.
InformationsquelleAutor Toby

Ich mag diesen Ansatz zu bauen, der Monate Tabelle:

SELECT 
  DATENAME(mm, date_val) AS month_name,  
  MONTH(date_val) AS month_number,  
  date_val as dt
FROM ( 
  SELECT DATEADD(mm, number, '2010-01-01') AS date_val
  FROM master.dbo.spt_values
  WHERE type = 'P'
  AND number BETWEEN 0 AND 11
) months

Basierend auf meinen tests, es ist schneller als eine CTE. Ich bin mit SQL Server 2008 Express.

Hier sind die Testergebnisse, die mithilfe von SET STATISTICS IO ON und SET STATISTICS TIME ON

CTE:

(12 row(s) affected)
Table 'Worktable'. Scan count 2, logical reads 73, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 64 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Unterabfrage:

(12 row(s) affected)
Table 'spt_values'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 4 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Obwohl Ihre ursprüngliche Frage ist zu Fragen, was dies heißt. Ich weiß nicht, einen Namen für ihn; vielleicht so etwas wie "left outer join gegen eine Serie?"

Einen weiteren Teil hinzufügen: wenn Sie sich gegen die Monate Tabelle, oder sogar, wenn Ihre original query, es ist in der Regel empfohlen, um zu vermeiden, mit einer Funktion wie JAHR([timestamp]) auf der linken Seite der WHERE-Klausel.

Also dieser code:

SELECT                     
    YEAR([timestamp]),                     
    MONTH([timestamp]),                     
    COUNT(*)                     
FROM table1                     
WHERE YEAR([timestamp]) = YEAR(GETDATE())                     
GROUP BY                     
    YEAR([timestamp]),                     
    MONTH([timestamp])

...bewirken, dass ein index scan (vorausgesetzt, timestamp indiziert ist), weil das JAHR([timestamp]) muss die Auswertung für jede Zeile. Auf einem 1m+ Zeile der Tabelle wird dies bedeuten, eine schlechte Leistung.

So werden Sie in der Regel eine Empfehlung wie dieses, statt:

SELECT                     
    YEAR([timestamp]),                     
    MONTH([timestamp]),                     
    COUNT(*)                     
FROM #table1                     
WHERE [timestamp] >= DATEADD(YY, DATEDIFF(YY, 0, GETDATE()), 0) -- First day of this year
AND   [timestamp] < DATEADD(YY, DATEDIFF(YY, 0, GETDATE()) + 1, 0) -- First day of next year
GROUP BY                     
    YEAR([timestamp]),                     
    MONTH([timestamp])

Diese verwenden eine index-Suche (wieder, vorausgesetzt, timestamp ist eine indizierte Spalte) und das Ergebnis in weniger logische Lesevorgänge und damit eine schnellere Reaktion. Dies bestätigt sich durch die überprüfung der execution plan.

Dank 8kb! Was zum erstellen einer Tabelle im Speicher, indem die Schleife durch beliebige ganze zahlen oder andere Werte, die Sie brauchen, und Links-Beitritt auf? Die memory-table-Ansatz scheint flexibler, und Sie immer wissen, was in der Tabelle. Eine Sache, die ich nicht gerne über die spt_values und rekursiven CTE-basierten Lösungen ist die Grenze. Es gibt keine Begrenzung auf eine Tabelle im Speicher.
In meiner Erfahrung, eine Zahlen-Tabelle oder Tally Tabelle ist in der Regel als ein best-practice-so würde ich Zustimmen. Aber in Bezug auf die Einschränkungen, die mit einem CTE, können Sie legen Sie den MAXRECURSION-Ebene bis zu 32676, die scheint mehr als ausreichend für die Termine. Ich verwendet, MAXRECURSION 2000 auf OMG ' s Abfrage und in der Lage war, 100 Jahre im Wert von Monate. Aber für spt_values, es ist wahr, Sie sind begrenzt auf 2048 Zeilen.

InformationsquelleAutor 8kb

Bin ich mit den KM, die für SQL Server 2005+ Sie könnte eine rekursive CTE:

WITH months AS (
  SELECT DATENAME(mm, '2010-01-01') AS month_name, 
         MONTH('2010-01-01') AS month_number, 
         CAST('2010-01-01' AS DATETIME) AS dt
  UNION ALL
  SELECT DATENAME(mm, DATEADD(mm, 1, m.dt)),
         MONTH(DATEADD(mm, 1, m.dt)),
         DATEADD(mm, 1, m.dt)
    FROM months m
   WHERE DATEADD(mm, 1, m.dt) <= '2010-12-01')
   SELECT x.month_name,
          y.*
     FROM months x
LEFT JOIN your_table y ON MONTH(y.date) = x.month_number

Nachdem alle, die Letzte Zeit KM & plauderten über dies - wir fanden eine rekursive CTE etwas effizienter als die Verwendung einer zahlen-Tabelle.

InformationsquelleAutor OMG Ponies

1

wie wäre es mit einer neuen Tabelle namens Monate: dann füllen Sie die Daten, die Sie beitreten können, um?
- Danke für die Anregung Randy. Ich habe das getan. Ich habe viele "komplette Serie" Tabellen und trat auf Sie zu. Ich hatte gehofft, es gibt einen besseren Weg. Auch ich hatte gehofft, jemand könnte mir sagen, was dieser Begriff heißt.
InformationsquelleAutor Randy

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.