SQL queries

[fonsde]

Καλησπερα θα ηθελα βοηθεια σε 2 queries.

 

1. Total quantity sold of Item X aggregated per state and for each quarter in year 2009

 

Οι πινακες ειναι time: day,week,month,quarter,year

location: city,state,country

item: itemname,brand

 

plain sql θελει , "χονδρικα" γιατι δεν δινει keys

[antonl]

Ασαφή και ελλειπή μου φαίνονται τα δεδομένα. Τα join πού θα γίνουν αν δε δίνει foreign keys πχ.

Ο πίνακας time τι χρησιμότητα/δεδομένα έχει; Οι πωλήσεις σε ποιον πίνακα αποθηκεύονται και πώς συνδέονται με την ημερομηνία πώλησης;

[Aztec]

Προς data warehouse μοιάζει η υλοποίηση. time, location, item dimensions. Λείπει το δέσιμο σε fact table με τα κατάλληλα measures.

[defacer]

Καλησπερα θα ηθελα βοηθεια σε 2 queries.

 

1. Total quantity sold of Item X aggregated per state and for each quarter in year 2009

 

Οι πινακες ειναι time: day,week,month,quarter,year

location: city,state,country

item: itemname,brand

 

plain sql θελει , "χονδρικα" γιατι δεν δινει keys

 

Θα κάνεις GROUP BY όλα τα aggregate columns (και WHERE πάνω σ' αυτά για να περιορίσεις το εύρος του query όπου χρειάζεται) και θα χρησιμοποιήσεις κάποια κατάλληλη aggregate function (π.χ. την SUM) πάνω σε όλα τα columns που θέλεις να κάνεις SELECT αλλά δεν συμμετέχουν στο GROUP BY.

 

Επίσης επειδή προφανώς δεν πρόκειται για το χόμπι σου εδώ, λέγοντάς μας ποιός και γιατί σου έδωσε να κάνεις το παραπάνω task καθώς και μέχρι που μπορείς να το πας μόνος σου φαίνεσαι λιγότερο ακαμάτης και περισσότερο άνθρωπος που δουλεύει αλλά απλά του λείπουν συγκεκριμένες γνώσεις (κάτι που δεν είναι έγκλημα).

[fonsde]

είναι οδός data warehousing. είναι ένα απλό ερώτημα μιας εργασίας, απλά μας το ρωτάνε χωρίς να μας έχουν διδάξει σχεδόν τίποτα πάνω στο θέμα sql.

 

με fact table τον sales

όπως:

 

 

SELECT location,time,item,SUM(QTY_SOLD)

FROM SALES

WHERE itemname = 'x' AND year = 2009

GROUP BY location,time,item

 

αυτό θα έκανα αλλα είπα να ρωτήσω και εδώ.

Είναι σωστό? Γιατί δεν μπορώ να το τεστάρω κάπου είναι scratch

[Aztec]

Λοιπόν η υλοποίηση για μένα είναι η εξής.

 

STAR SCHEMA

>
Time Dimension (Table)

id   day   week  month  quarter  year
-------------------------------


Location Dimension 

id   city   state_id state  country_id country    -- Εδώ μπορείς να βάλεις κλειδιά για state , country εκτός από το όνομα 
---------------------------------------------------- Επίσης αν θες κάνεις join με τον πίνακα country, state και να πας για snowflake schema   


Item Dimension 

id   itemname  id_brand brand    -- Το ίδιο με παραπάνω και για το brand
-------------------------------


SALES FACT TABLE

TIME_ID, LOCATION_ID , ITEM_ID , QTY_SOLD , etc 
-------------------------------------------

 

 

QUERY

 

>SELECT LOCATION.STATE_ID,LOCATION.STATE,TIME.QUARTER,SUM(SALES.QTY_SOLD)
FROM SALES, TIME, LOCATION 
WHERE SALES.TIME_ID=TIME.ID
AND TIME.YEAR=2009
AND SALES.LOCATION_ID=LOCATION.ID
AND SALES.ITEM_ID='XYZ'                    ----- ή κάνεις join και το ITEM Και πας με το όνομα αντί με τον κωδικό
GROUP BY LOCATION.STATE_ID,LOCATION_STATE,TIME.QUARTER

 

Υπάρχουν και άλλες προσεγγίσεις φυσικά. Αυτή είναι μια γρήγορη που έκανα, Το id στο time dimension είναι ενα surrogate key.Ένα αυτόματα δημιουργούμενο κλειδί. Μπορείς να τα βάλεις και όλα flat χωρίς σπασίματα σε πίνακες με κλειδιά. Τέσπα μια ιδέα πιστεύω ότι την πήρες

[fonsde]

Καλησπερα aztec, το κατανοω αυτο που εχεις γραψει, απλα επειδη δεν εχω καθολου γνωση πανω στην υλοποιηση sql σε warehousing.

Εισαι σιγουρος πως ειναι σωστη η μεθοδος αυτη στο data warehousing. Απλα θελω να σιγουρευτω για να δουλεψω ετσι.

 

thnx again

[Aztec]

Σε μερικά λεπτα που το εγραψα χωρίς πολλές πληροφοριες πιστεύω πως είναι μια λογική προσέγγιση. Γενικά η λογική είναι dimensional modeling ακολουθώντας star schema με ημερήσιο time granularity . Είναι ολόκληρος τομέας και δεν μπορώ να πω και πολλά .

 

Λέξεις για googling

dimensional modeling, dimension, slow changing dimension, hierarchy, fact table , measures ,oltp vs olap , star schema , snowflake schema , denormalized schema ,constellation schema , time dimension, time granularity, surrogate key, etl .

 

Προσπαθησε να φτιάξεις σωστά ένα star schema με ορθά dimensions και fact table και τελειωσες. 4 μικροί πίνακες είναι

 

Αν απλά είναι ερώτηση στο να γράψεις SQL statement με ετοιμο το σχήμα θα πρέπει να δώσεις το σχήμα . Διαφορετικά δεν μπορεί κανείς να σε βοηθησει.

[fonsde]

το schema ειναι snowflake

(εγω το σχεδιασα)

 

>Time 
[time_key, day, week, month, quarter, year]

Location
[location_key, city, state_key] 

State
[state_key, state, country_key] 

Country
[country_key, country] 

Item
[item_key, item_name, brand_key] 

Brand
[brand_key, brand]

Sales Fact Table
[time_key, location_key, item_key, branch_key, money_sold, qty_sold]  

 

 

εκανα και το query

 

 

>SELECT Time.quarter, State.state, Sum(Sales.qty_sold)
FROM Time, State, Sales
WHERE Sales.time_key = Time.time_key
AND Time.year = 2009
AND Sales.location_key = Location.location_key
AND Location.state_key = State.state_key
AND Sales.item_key = Item.item_key
AND Item.item_name = 'X'          
GROUP BY Time.quarter, State.state

 

σωστο?

δεν εχω βαλει στο select το key, ειναι απαραιτητο?

 

thnx again

[Aztec]

Έτσι βαρύ χιονάκι... Μια χαρά το βλέπω ! Στο query έχεις ξεχάσει να βάλεις στο FROM τους πίνακες ITEM,LOCATION . Αν το όνομα του STATE είναι unique δεν χρειάζεται τίποτα. Καλύτερα όμως να βάλεις στο group by State.state_key, State.state. Δηλαδή να γίνει ως εξής με τις δύο διορθώσεις

 

SELECT Time.quarter, State.state, Sum(Sales.qty_sold)

FROM Time, State, Sales ,Item,,Location

WHERE Sales.time_key = Time.time_key

AND Time.year = 2009

AND Sales.location_key = Location.location_key

AND Location.state_key = State.state_key

AND Sales.item_key = Item.item_key

AND Item.item_name = 'X'

GROUP BY Time.quarter, State.state_key, State.State

 

Επίσης έχεις βάλει branch key στον fact table που σημαίνει ότι μάλλον σου λείπει κάποιο dimension. Αν βαλεις branch dimension(που μοιάζει λογικό) πρέπει το location να μεταφερθεί σε αυτό και να φύγει απο το fact table.

 

Α και για καλύτερα μετονόμασε το LOCATION σε CITY και το ITEM σε PRODUCT. Πολύ καλή δουλειά πάντως

[taazz]

Επίσης έχεις βάλει branch key στον fact table που σημαίνει ότι μάλλον σου λείπει κάποιο dimension.

 

Μπααα! πιο πιθανό το κόβω να εννοεί brand_key και να είναι τυπογραφικό λάθος.

[fonsde]

>Time 
[time_key, day, week, month, quarter, year]

Location
[location_key, city, state_key] 

State
[state_key, state, country_key] 

Country
[country_key, country] 

Item
[item_key, item_name, brand_key] 

Brand
[brand_key, brand]

Sales Fact Table
[time_key, location_key, item_key, money_sold, qty_sold]  

 

εχω ακομα ενα query.

find the most profitable brands aggregated by city in 2009

 

>SELECT Brand.brand, Location.city, Max(Sales.money_sold)
FROM Time, Location, Sales, Item, Brand
WHERE Sales.time_key = Time.time_key
AND Time.year = 2009
AND Sales.item_key = Item.item_key
AND Item.brand_key = Brand.brand_key
AND Sales.location_key = Location.location_key
GROUP BY Location.city, Brand.brand

 

δεν αλλαξα τιποτα στο snowflake.

 

Ευχαριστω και παλι!

[taazz]

1) το πιο κερδοφόρο πως ορίζεται? αυτό με τις περισσότερες πωλήσεις τα μεγαλήτερα κέρδη ή το μεγαλήτερο τζίρο?

2) Τα θέλει όλα ή έχει ενα ελάχιστο ποσό που από εκεί και κάτω δεν πρέπει να εμφανίζεται?

 

Σε γενικές γραμμές το κερδοφόρο υπολογίζεται σαν τιμή πώλησης - κόστος αγοράς και διάθεσης.

Στην απλούστερη μορφή θα πρέπει να βρείς το προϊόν με το μεγαλήτερο τζίρο ή sum(Sales.Money_sold)

to Max επιστρέφει την πιο ακριβή πώληση.

[Aztec]

Δεν θα παίξει σωστά. Αυτο θα σου γυρίσει ανα city το brand με το μεγαλύτερο ποσό ημέρας και όχι όλου του χρονου. Πρέπει πρώτα να αθροίσεις και μετά να βρεις το μέγιστο του αθροίσματος.

 

Θέλεις max στο sum ή analytical function κατάταξης βάσει του ποσού.

[fonsde]

αρα απλα βαζω Max(sum(Sales.money_sold))?