Brauche Hilfe bei einem Uni Aufgabenblatt, SQL Abfragen aus Star-Schema

[Julian_HTW_Student]

Hi,

wir haben für die Uni aus einer existierenden Datenbank ein Star-Schema erstellt und müssen nun in einem Aufgabenblatt folgende Abfragen via SQL machen.

Dafür bräuchte ich mal ein paar Tipps für die Abfrage. Leider hat (Aufgabenblatt davor) es nicht wircklich geklappt das in unser Star-Schema Daten geschrieben werden. Die Abfragen kann ich natürlich trotzdem erstellen. Allerdings wird rumprobieren schwer, da ich zwar Syntax-Fehler angezeigt bekomme, aber keine Daten (Da die Tabellen leer sind).

Die Abfragen lauten wie folgt und es hängt ein Screenshot an, wie das Ergebnis ausschauen soll.

a.)Berechnen Sie den durchschnittlichen Umsatz pro Tag für die Montage des Jahres
2010 sowie den gesamten durchschnittlichen Umsatz pro Tag.

So soll die Lösung aussehen:

b.)Ermitteln Sie die Anzahl der Bestellungen pro Region der letzten zwei Quartale
des Jahres 2008. Es sind auch Regionen auszugeben, die ggf. nur in einem Quartal
beliefert wurden.

So soll die Lösung aussehen:

c.) Welche Produktsubkategorien machen die Top-20% der Erträge der Firma aus?

So soll die Lösung aussehen:

Im ersten Schritt habe ich mir aus meinen Dimensionen und der Faktentabelle eine Sicht erstellt, ich hänge mal einen Screenshot an.

Wäre super wenn ihr mir bei der Erarbeitung helfen könntet.

Lieben Gruß

 

[akretschmer]

Hi,

wir haben für die Uni aus einer existierenden Datenbank ein Star-Schema erstellt und müssen nun in einem Aufgabenblatt folgende Abfragen via SQL machen.

Lieben Gruß

a) Du suchst avg() und eine Condition (also WHERE), die aus den datum-Angaben alle Montage rausfiltert. In PG wäre das z.B. mit exctract() möglich. Allerdings würde das beim 267. Dezember wohl scheitern. Falls die Tabelle eh schon nur die Montage enthält brauchst kein WHERE. Glückspilz!

b) 2 Zahlen zu addieren überlasse ich Dir zur Übung. Schaffst Du!

c) ist die Tabelle noch länger? Du addierst die Prozente, bis es über 20 sind. Dazu gibt es Window-Funktionen.

 

[Julian_HTW_Student]

Also zum Nachtrag: Die Lösungstabellen wurden vorgegeben, mir fehlt die komplette SQL Abfrage.

Hab die SQL Abfragen jetzt soweit

a.)
SELECT Orderdate, AVG(ListPRICE) SalesPerDay
FROM dbo.Cube
WHERE DATENAME(WEEKDAY, OrderDate) = 'Monday'
AND YEAR(OrderDate) = 2010
GROUP BY OrderDate

Ich bekomme eine leere Tabelle mit den beiden Spalten
Orderdate / SalesPerDay

Würde mir diese Abfrage nur die Montage als Datum ausgeben und die Umsätze dementsprechend zusammensummieren? (s. Screenshot oben)
Ich hätte jetzt getippt das mir die Abfrage beim Datum nur die Montage auswirft aus 2010, aber bei den Werten für die Umsätze müsste ich doch eig die Spalte Umsatz aus meinem dbo.Cube ausgeben. Wie würde ich unten dran noch eine weitere Reihe ausgeworfen bekommen, die mir dann auch noch den Umsatz im Durchschnit angibt?

Sprich Ausgabe

Datum / Umsatz Durchschnitt
Datum1 / Wert 1
Datum2 / Wert 2
...
AVG / AVG Wert

b.)
SELECT CountryRegionCode
,SUM(CASE WHEN DATEPART(QQ, OrderDate) = 3 AND YEAR(OrderDate) = 2008 THEN ListPRICE END) Q3Average
,SUM(CASE WHEN DATEPART(QQ, OrderDate) = 4 AND YEAR(OrderDate) = 2008 THEN ListPRICE END) Q4Average
FROM dbo.Cube
GROUP BY CountryRegionCode

Ich erhalte eine Tabelle mit den drei Spallten
CountryRegionCode / Q3Avg / Q4Avg

Hier könnte ich mir vorstellen das die Abfrage funzt.

Hätte gerne noch die Spalten umbenannt, würde das nicht mit der AS Funktion bei SELECT laufen?

c.)
SELECT TOP 20 PERCENT
CategoryName
,SUM(ListPRICE) TotalSalesPerCat
FROM dbo.Cube
GROUP BY CategoryName
ORDER BY 2 DESC

Ich erhalten eine Tabelle mit den zwei Spalten

CategoryName / TotalSalesPerCat

Würde ich hier %-Werte oder eine aufaddierte Summe erhalten?

 

[ukulele]

a.)
SELECT Orderdate, AVG(ListPRICE) SalesPerDay
FROM dbo.Cube
WHERE DATENAME(WEEKDAY, OrderDate) = 'Monday'
AND YEAR(OrderDate) = 2010
GROUP BY OrderDate

Ich bekomme eine leere Tabelle mit den beiden Spalten
Orderdate / SalesPerDay

Würde mir diese Abfrage nur die Montage als Datum ausgeben und die Umsätze dementsprechend zusammensummieren? (s. Screenshot oben)
Ich hätte jetzt getippt das mir die Abfrage beim Datum nur die Montage auswirft aus 2010, aber bei den Werten für die Umsätze müsste ich doch eig die Spalte Umsatz aus meinem dbo.Cube ausgeben. Wie würde ich unten dran noch eine weitere Reihe ausgeworfen bekommen, die mir dann auch noch den Umsatz im Durchschnit angibt?

Sprich Ausgabe

Datum / Umsatz Durchschnitt
Datum1 / Wert 1
Datum2 / Wert 2
...
AVG / AVG Wert

Deine avg() Funktion berücksichtigt nur die Werte die die Abfrage vorgibt. Also auch nur alle Werte die Montage betreffen bzw. die WHERE Klausel erfüllen. Mit UNION könntest du jetzt noch eine Summe anhängen, die müsste aber die selben Bedingungen berücksichtigen.

SELECT    Orderdate,
        AVG(ListPRICE) AS SalesPerDay
FROM    dbo.[Cube]
WHERE    DATENAME(WEEKDAY, OrderDate) = 'Monday'
AND        YEAR(OrderDate) = 2010
GROUP BY OrderDate
UNION ALL
SELECT    NULL AS Orderdate,
        AVG(ListPRICE) AS SalesPerDay
WHERE    DATENAME(WEEKDAY, OrderDate) = 'Monday'
AND        YEAR(OrderDate) = 2010

Allerdings muss deine angehängt Spalte das selbe Format haben wie die Ausgabe des Selects. Wenn du hier bei Datum statt NULL sowas wie 'Summe' rein schreibst wird er meckern.

b.)
SELECT CountryRegionCode
,SUM(CASE WHEN DATEPART(QQ, OrderDate) = 3 AND YEAR(OrderDate) = 2008 THEN ListPRICE END) Q3Average
,SUM(CASE WHEN DATEPART(QQ, OrderDate) = 4 AND YEAR(OrderDate) = 2008 THEN ListPRICE END) Q4Average
FROM dbo.Cube
GROUP BY CountryRegionCode

Ich erhalte eine Tabelle mit den drei Spallten
CountryRegionCode / Q3Avg / Q4Avg

Hier könnte ich mir vorstellen das die Abfrage funzt.

Hätte gerne noch die Spalten umbenannt, würde das nicht mit der AS Funktion bei SELECT laufen?

Mit AS Alias wäre hier der richtige Weg. Manchmal kann oder muss man das AS auch weg lassen aber mit Q3Average wurde hier schon ein Alias verwendet. Den kannst du ändern.

c.)
SELECT TOP 20 PERCENT
CategoryName
,SUM(ListPRICE) TotalSalesPerCat
FROM dbo.Cube
GROUP BY CategoryName
ORDER BY 2 DESC

Ich erhalten eine Tabelle mit den zwei Spalten

CategoryName / TotalSalesPerCat

Würde ich hier %-Werte oder eine aufaddierte Summe erhalten?

Du würdest die Summen aller Produktkategiren bekommen die den obersten 20% der Tabelle angehören, sortiert nach den Summen. Ich bin mir grad nicht sicher, ob das Sinn ergibt ohne Testdaten

 

[Julian_HTW_Student]

Also ich habe die Aufgaben jetzt so weit gelöst.
Wir haben ein Star Schema zur Verfügung gestellt bekommen, damit wir auch Daten abrufen können.
Von daher neue Verknüpfungen, aber hier mal meine 3 SQL Abfragen:

Abfrage 1:

SELECT Datum, ROUND((AVG (VERKAUFSWERT * VERKAUFSMENGE)),2) Umsatz
FROM Data_Star_Awesome.dbo.VERKAUFSFAKTEN a
Join Data_Star_Awesome.dbo.DIMDATUM c on c.DATUMID=a.DATUMID
WHERE DATEDIFF(dd,0, DATUM)%7=0 AND JAHR = 2010
GROUP BY Datum

Ausgabe:
Spalte Datum / Umsatz
Zeile1: 2010-11-1 / 324,670000

Problem1: Ich bekomme es nicht hin, dass mir im Ergebnis die Umsätze mit nur 2 Nachkommastellen angegeben werden, d.h. er sagt bspw. Umsatz Zeile1 ist 324,67. Also gerundet und auf nur zwei Nachkommastellen.

Problem2: I hätte gerne in meiner Ausgabe die Zeilen 1 - n und die entsprechenden Umsätze und am Ende noch eine Zeile die Alle Umsätze gerundet anzeigt, also aufsummiert.

Lösungsvorschläge?

Abfrage 2:

SELECT Region
,SUM(CASE WHEN QUARTAL = 3 AND JAHR = 2008 THEN VERKAUFSMENGE END) Q3_2008
,SUM(CASE WHEN QUARTAL = 4 AND JAHR = 2008 THEN VERKAUFSMENGE END) Q4_2008
From
Data_Star_Awesome.dbo.VERKAUFSFAKTEN a
Join Data_Star_Awesome.dbo.DIMKUNDE b on b.KUNDEID=a.KUNDEID
Join Data_Star_Awesome.dbo.DIMDATUM c on c.DATUMID=a.DATUMID
Group by REGION

Funktioniert.

Abfrage 3:

SELECT TOP 20 Percent Unterkategorie , CAST((view2.vorkommen * 100 / view1.anzahl)AS DECIMAL (4,2))Prozent
FROM
(Select SUM(Verkaufswert) anzahl
FROM Data_Star_Awesome.dbo.VERKAUFSFAKTEN a) view1,
(Select Unterkategorie ,SUM(Verkaufswert) vorkommen
FROM Data_Star_Awesome.dbo.VERKAUFSFAKTEN a
Join Data_Star_Awesome.dbo.DIMPRODUKT c on c.Produktid=a.Produktid
GROUP BY Unterkategorie) view2;

Problem / Frage, hier gibt er mir die Ausgabe in 2 Nachkommastellen an. Rundet der Cast befehl auch? Habe den Befehl aus dem Vorlesungsmaterial entnommen, bin jedoch nicht 100%ig sicher ob das so richtig ist.