Newsgroup: it.comp.software.database
Titolo: Così per parlare: il lotto!

...dicevo, è giusto uno di quegli argomenti di cui si discute davanti 
alla macchina del caffè:
Se voi doveste buttar giù una struttura dati SQL atta a contenere un 
archivio di estrazioni del lotto, come vi regolereste?
In pratica sono x serie di y numeri, ognuno caratterizzato da una coppia 
di "coordinate" (nome ruota, ordine estrazione) con un set predefinito 
di valori, e ogni serie è caratterizzata dalla data e/o dal numero 
progressivo di estrazione.
Non è certo un problema "difficile" ma comunque più ci penso e meno 
riesco a decidermi su quale sia la soluzione migliore, nel senso che poi 
permetta di fare query più semplici e più efficaci su tutto l'archivio, 
considerando che si possono fare molteplici tipi di ricerca.
Ogni opinione è ben accetta ;-)
-- 
Fabrizio "Hermooz" Ermini

 considerando che si possono fare molteplici tipi di ricerca.
   Il problema da risolvere e` se considerare i numeri estratti come 
appartenenti allo stesso dominio (relazione #1) o a dominii differenti 
(relazione #2). Probabilmente, ti conviene prima "buttare giu`" tutte le 
ricerche possibili ed immaginabili che vorrai fare e poi disegnare la 
base di dati.
<code>
	-- relazione #1
	create table estrazioni (
	  estrazione int      primary key,
	  ruota      char(8)  not null,
	  posizione  smallint not null,
	  estratto   smallint not null
	)
 
	-- relazione #2
	create table estrazioni (
	  estrazione   int      primary key unique,
	  ruota        char(8)  not null,
	  i_estratto   smallint not null,
	  ii_estratto  smallint not null,
	  iii_estratto smallint not null,
	  iv_estratto  smallint not null,
	  v_estratto   smallint not null
	)
	-- relazione ausiliaria date
	create table date (
	  estrazione int  primary key unique
	    references estrazioni(estrazione),
	  giorno     date not null
	)
</code>
-- 
Alla prossima,
Marco

 considerando che si possono fare molteplici tipi di ricerca.
   Il problema da risolvere e` se considerare i numeri estratti come 


 
Infatti questo era il vero senso del mio discorso: tentare di 
individuare le ricerche piů frequenti...
Ciao!
-- 
Fabrizio "Hermooz" Ermini

 individuare le ricerche più frequenti...
   Immagino che la cosa piu` interessante sia valutare ritardi e 
frequenze, di solito queste sono le cose piu` "richieste" dagli utenti, 
per cui la posizionalita` non e` fondamentale (quindi relazione #1 
riveduta e corretta):
<code>
	create table estrazioni (
	  estrazione int,
	  ruota      char(8),
	  estratto   smallint not null,
	  primary key(estrazione,ruota)
	)
	create table giorni (


...
Se puo' interessare, sul mio sito c'e' uno script per divertirsi a
creare schedine con Oracle:
http://www.b3soft.com/phpnuke/modules.php?name=Downloads&d_op=viewdownload&cid=5
vengono create delle tabelle, quindi una procedura sulla base di tre
numeri chiave genera il sistema (e' una mia idea, garantisce il 3 se
escono tutti e 6 i numeri su 30 ca., genera un centinaio di colonne)
E anche una funzione che passandogli i numeri usciti controlla i
risultati.
Greetings,
Alessandro Trebbi
Pesaro, Italy
http://www.b3soft.com

Salve,
a mio avviso, considerato che i dati in question non sono volatili - ovvero
una volta inseriti non cambiano - e sono soggetti necessari esclusivamente ad
elaborazioni di tipo analistico (cosa - ovvero che numero/quando - ovvero in
che data/dove - ovvero su che ruota), la struttura più adatta è quella
adottata nei sistemi di data warehousing.
Di conseguenza le tabelle necessarie - almeno in questo caso - sono quattro:
una dimensione per il calendario, una dimensione per le ruote, una dimensione
per i numeri ed una tabella di fatti per le estrazioni vere e proprie.
In pratica (per Interbase/Firebird):
CREATE TABLE dateestr (
  dateestr_id DATE NOT NULL,
  giorno_mese SMALLINT,
  giorno_settimana SMALLINT,
  giorno_anno SMALLINT,
  settimana_anno SMALLINT,
  mese_anno SMALLINT,
  anno SMALLINT,
  trimestre SMALLINT,
  quadrimestre SMALLINT,
  semestre SMALLINT
);
ALTER TABLE dateestr ADD PRIMARY KEY (dateestr_id);
CREATE ASCENDING INDEX idx_dateestr_gdm ON dateestr (giorno_mese);
CREATE ASCENDING INDEX idx_dateestr_gds ON dateestr (giorno_settimana);
CREATE ASCENDING INDEX idx_dateestr_gda ON dateestr (giorno_anno);
CREATE ASCENDING INDEX idx_dateestr_sda ON dateestr (settimana_anno);
CREATE ASCENDING INDEX idx_dateestr_mda ON dateestr (mese_anno);
CREATE ASCENDING INDEX idx_dateestr_ann ON dateestr (anno);
CREATE ASCENDING INDEX idx_dateestr_tri ON dateestr (trimestre);
CREATE ASCENDING INDEX idx_dateestr_qua ON dateestr (quadrimestre);
CREATE ASCENDING INDEX idx_dateestr_sem ON dateestr (semestre);
CREATE TABLE ruote (
  ruote_id SMALLINT NOT NULL,
  ruote_des VARCHAR(20)
);
ALTER TABLE ruote ADD PRIMARY KEY (ruote_id);
CREATE TABLE numeri (
  numero SMALLINT NOT NULL,
);
ALTER TABLE numeri ADD PRIMARY KEY (numero);
CREATE TABLE estrazioni (
  ruote_id SMALLINT NOT NULL,
  dateestr_id DATE NOT NULL,
  I_ESTR SMALLINT,
  II_ESTR SMALLINT,
  III_ESTR SMALLINT,
  IV_ESTR SMALLINT,
  V_ESTR SMALLINT
);
/* nessuna primary key per la tabella estrazioni */
ALTER TABLE estrazioni ADD FOREIGN KEY (ruote_id) REFERENCES ruote (ruote_id);
ALTER TABLE estrazioni ADD FOREIGN KEY (dateestr_id) REFERENCES dateestr
(dateestr_id);
/* gli indici sono consigliabili */
CREATE INDEX idx_estrazioni_ruote ON estrazioni (ruote_id);
CREATE INDEX idx_estrazioni_dateestr ON estrazioni (dateestr_id);
Il motivo di una tabella calendario così "dettagliata" è semplice: torna
estremamente più utile nei raggruppamenti e, se correttamente indicizzata, li
rende molto più veloci rispetto all'utilizzo di una funzione che estragga
dalla data una delle informazioni già presenti nella tabella (mese dell'anno
ad esempio). Consente inoltre raggruppamenti particolari, quali "quante volte
è uscito un certo numero in un determinato semestre.
Il tutto è stato scritto a mano senza verifiche, quindi non garantisco che sia
esente da errori. ;)
Se poi dovessimo pensare di utilizzare Oracle al posto di Firebird/Interbase,
avremmo a disposizione anche funzioni particolarmente avanzate per analizzare
i dati presenti (GROUP BY CUBE/ROLLUP, materialized views, create
fact/dimension ecc.).
Tuttavia, anche con una struttura del genere estrarre i dati in fase di
analisi diventa davvero semplice. Ad esempio:
Tutte le estrazioni, in ordine di data/ruota:
    select r.ruote_des,
           d.dateestr_id,
           e.i_estr,
           e.ii_estr,
           e.iii_estr,
           e.iv_estr,
           e.v_estr
      from (estrazioni e left join dateestr d
        on d.dateestr_id = e.dateestr_id) left join ruote r
        on r.ruote_id = e.ruote_id
  order by d.dateestr_id,
           r.ruote_id
I ritardi maggiori, in ordine (dal numero più ritardatario con relativa ruota
al meno ritardatario con relativa ruota, ed è qui che la tabella numeri
diventa essenziale; qui conviene creare una view):
create view v_ritardi_maggiori (ruota, numero, ultima_estr) as
  select r.ruote_des,
         n.numero,
         max(e.dateestr_id)
    from (estrazioni e left join numeri n
      on (e.i_estr   = n.numero
      or  e.ii_estr  = n.numero
      or  e.iii_estr = n.numero
      or  e.iv_estr  = n.numero
      or  e.v_estr   = n.numero)) left join ruote r
      on r.ruote_id = e.ruote_id
group by r.ruote_des,
         n.numero;
per poi effettuare le select con:
  select *
    from v_ritardi_maggiori
order by ruota,
         ultima_estr
che mostra i dati della vista con l'ordinamento per ruota/ultima estrazione
del numero (da quello estratto meno recentemente a quello estratto più
recentemente).
Il totale estrazioni per ruota/numero:
  select r.ruote_des,
         n.numero,
         count(*) tot_estrazioni
    from (estrazioni e left join numeri n
      on (e.i_estr   = n.numero
      or  e.ii_estr  = n.numero
      or  e.iii_estr = n.numero
      or  e.iv_estr  = n.numero
      or  e.v_estr   = n.numero)) left join ruote r
      on r.ruote_id = e.ruote_id
group by r.ruote_des,
         n.numero
Il numero maggiormente ritardatario per ogni ruota (query molto pesante, da
utilizzare dopo aver creato la vista v_ritardi_maggiori):
  select *
    from v_ritardi_maggiori rm
   where ultima_estr = (select min(rm2.ultima_estr) from v_ritardi_maggiori
rm2 where rm.ruota = rm2.ruota)
order by rm.ruota,
         rm.ultima_estr
...e via discorrendo (mille altre cose che non mi vengono in mente).
Si potrebbero poi realizzare delle tabelle aggiornate tramite procedure che
contengano solo dei sommari, ad esempio il maggior ritardatario per una ruota
o il numero più frequente.
Fatemi sapere se avete altre idee. ;)
Saluti,
Ender

          n.numero;
Ah, ecco... ora comincio a capire...
Grazie delle idee, e ciao!
-- 
Fabrizio "Hermooz" Ermini

 Grazie delle idee, e ciao!
:)
Saluti,
Ender