Gagnasafnsfræði, haust 2011

Verkefni 4 - Forritun á móti SQL

Skiladæmi:

Skilgreinið töflu til að halda utan um opinbert viðmiðunargengi íslensku krónunnar gagnvart vissum gjaldmiðlum.
Taflan skal innihalda eftirfarandi dálka:
1. dags: dagsetning
2. myntnafn: þrír stafir (t.d. USD, GBP)
3. kaupgengi: tala með tveimur aukastöfum (t.d. 135.98)
4. midgengi: tala með tveimur aukastöfum
5. solugengi: tala með tveimur aukastöfum
Notið numeric tagið í PostgreSQL til að geyma gengistölurnar.
Það er ein skráning á dag per myntnafn. Hagið aðallykli töflunnar samkvæmt því.
Sýnið CREATE TABLE skipunina á læsilegu formi með inndrætti.
Lausn:
```
CREATE TABLE gengi (
    dags DATE NOT NULL,
    myntnafn CHAR(3) NOT NULL
    kaupgengi NUMERIC(8,2) NOT NULL,
    midgengi NUMERIC(8,2) NOT NULL,
    solugengi NUMERIC(8,2) NOT NULL,
    PRIMARY KEY (dags, myntnafn)
);
```
Það er hægt að sækja opinbert viðmiðunargengi íslensku krónunnar í dag á XML formi frá vefþjónustu Seðlabankans á eftirfarandi slóð:
http://www.sedlabanki.is/?PageID=289

Einnig er hægt að sækja viðmiðunargengið ákveðinn dag með því að bæta við stikanum dagur=DD.MM.YYYY.
Dæmi:
http://www.sedlabanki.is/?PageID=289&dagur=25.01.2007
Skrifið forrit í Java, Python, PHP eða öðru forritunarmáli að ykkar vali sem virkar á eftirfarandi hátt:
1. Forritið keyrir í skipanalínu sem sjálfstætt forrit
2. Forritið tekur eitt viðfang: dagsetningu á forminu YYYY-MM-DD
3. Forritið sækir opinbert viðmiðunargengi frá vefþjónustu Seðlabankans
4. Forritið þáttar XML gögnin og skráir viðmiðunargengi USD, GBP, CHF og JPY í PostgreSQL töfluna úr dæmi 1
Lausnir:
Búið til eftirfarandi töflu sem heldur utan um pantanir:
```
CREATE TABLE pantanir (
    id SERIAL PRIMARY KEY,
    customer INTEGER NOT NULL,
    dags DATE NOT NULL,
    vorunumer INTEGER NOT NULL,
    mynt CHAR(3) NOT NULL,
    verd NUMERIC(8,2) NOT NULL,
    magn INTEGER NOT NULL
);
```
Hlaðið inn skránni pantanir.csv (sem inniheldur 10.000 skáldaðar pantanir) með eftirfarandi COPY skipun:
```
COPY pantanir (dags, customer, vorunumer, mynt, verd, magn)
   FROM 'pantanir.csv'
   DELIMITER '|';
```
ATH. Það gæti þurft að slá inn slóðina (full path) á pantanir.csv ef skráin er ekki í vinnumöppunni. Gerið þá /home/xx/.. í Linux/MacOS X eða c:/xx/.. í Windows.

Sýnið SQL skipun sem sækir eftirfarandi töflu: fyrir hvert vörunúmer, mynt og verð, reiknið fjölda pantana, heildarmagn og heildarupphæð (verð*heildarmagn) í júlí (2011-07). Raðið eftir heildarupphæð í lækkandi röð.

Útkoman ætti að vera nákvæmlega sú tafla sem sést hér fyrir neðan:

 vorunumer | mynt | verd  | fjoldi | magn  |   total    
-----------+------+-------+--------+-------+------------
    221517 | USD  | 29.99 |    733 | 36110 | 1082938.90
    273140 | USD  |  8.99 |   1450 | 74086 |  666033.14
    732052 | GBP  | 15.99 |    679 | 34327 |  548888.73
    158173 | USD  | 17.52 |    462 | 23089 |  404519.28
    601314 | GBP  | 19.99 |    242 | 12061 |  241099.39
    770291 | CHF  |  2.99 |    961 | 49041 |  146632.59
    526734 | CHF  |  3.25 |    241 | 12249 |   39809.25
(7 rows)

Ábending: Það er hægt að nota TO_CHAR fallið í PostgreSQL til að vinna með dagsetningar.

Lausn:

SELECT vorunumer,
       mynt,
       verd, 
       COUNT(*) AS fjoldi,
       SUM(magn) AS heildarmagn,
       SUM(magn)*verd AS heildarupph
FROM pantanir
WHERE TO_CHAR(dags, 'YYYY-MM')='2011-07'
GROUP BY vorunumer, mynt, verd
ORDER BY heildarupph DESC;

Útkoma:

 vorunumer | mynt | verd  | fjoldi | heildarmagn | heildarupph 
-----------+------+-------+--------+-------------+-------------
    221517 | USD  | 29.99 |    733 |       36110 |  1082938.90
    273140 | USD  |  8.99 |   1450 |       74086 |   666033.14
    732052 | GBP  | 15.99 |    679 |       34327 |   548888.73
    158173 | USD  | 17.52 |    462 |       23089 |   404519.28
    601314 | GBP  | 19.99 |    242 |       12061 |   241099.39
    770291 | CHF  |  2.99 |    961 |       49041 |   146632.59
    526734 | CHF  |  3.25 |    241 |       12249 |    39809.25
(7 rows)

Notið forritið ykkar sem sækir viðmiðunargengið fyrir gefna dagsetningu til að skrá inn gengið fyrir dagsetningar 2011-08-01, 2011-08-02 og 2011-08-03.
Sýnið SQL skipun sem notar ytri tengingu (OUTER JOIN) til að sækja lista yfir allar dagsetningar+myntir í pöntunum sem eru ekki með neitt gengi skráð í gengistöfluna. Lausn:
```
SELECT DISTINCT p.dags, p.mynt
FROM pantanir p LEFT JOIN gengi g
  ON p.dags=g.dags AND p.mynt=g.myntnafn
WHERE g.dags IS NULL;
```
Útkoma:
```
    dags    | mynt 
------------+------
 2011-07-01 | CHF
 2011-07-01 | GBP
 2011-07-01 | USD
...
```
Sýnið SQL skipun sem sækir eftirfarandi töflu: fyrir hvern mánuð, reiknið heildarupphæð í íslenskum krónum fyrir allar pantanir (notið miðgengið til að breyta yfir í krónur).
Taflan ætti að vera ca. svona:
```
 month   | total
---------+--------
 2011-08 | XXXX
```
ATH: Ég geri ráð fyrir að þið séuð bara með gengi skráð fyrir dagsetningar 2011-08-01, 2011-08-02 og 2011-08-03.
Ég geri einnig ráð fyrir að þið notið venjulega innri tengingu (INNER JOIN) og þá eru pantanir án gengis ekki teknar með í útreikningi, sbr. tengireglur fyrir INNER JOIN.
Lausn:
```
SELECT TO_CHAR(p.dags, 'YYYY-MM') as month,
       SUM(p.verd*p.magn*g.midgengi) AS total
FROM pantanir p, gengi g
WHERE p.dags=g.dags 
  AND p.mynt=g.myntnafn
GROUP BY month;
```
Útkoma:
```
  month  |     total     
---------+---------------
 2011-08 | 41401576.2783
(1 row)
```