Loomingu Raamatukogu 60

Hakkasime kodus hiljaaegu raamatuid kirja panema, mis meil on. Et leiaks kiiresti, mis on, kus on ja mis puudu või topelt.

Pealt 7000 raamatu sai kirja – ootel veel umbes tuhatkond Loomingu Raamatukogu.

Ei viitsind neid käsitsi sisse toksida, seepärast otsisin, kust leiaks – tean et on olemas LR Bibliograafia.

LR 60 bibliograafia leiab siit: http://www.loominguraamatukogu.ee/wp-content/uploads/2017/02/LRbiblio.pdf

Väga kena, aga mul oleks vaja sealt need raamatud ja nimed ja muu vajalik tabelisse saada. Saaks otsida ja filtreerida ja märkida, mis mul puudu jne.

Copy-Paste sealt väga ei õnnestu ja mulle väga ei meeldi ka. Seepärast võtsin oma vana sõbra Exceli ja palusin tal mulle vajalik sealt sisse lugeda. Utlesin, et mul on selline PDF ja tahaks sealt andmed kätte saada.

Kõrvalaknas uurisin natuke seda ja katsetasin – seal umbes 93 lkst alates ja leheküljeni 147 on ilus kronoloogiline loetelu (mul kahjuks aastad 1957-2000, hilisemaid ei ole – muidu see loetelu leheküljeni 164)

Selleks filtreerin välja ainult leheküljed (tabelid on seal vaid päised ja jalused), teisendan Page006 → 6 täisarvuks ja lisan filtri – anna vaid lehed 93-147

Seejärel eemaldan ülearused veerud ja teisendan sisu tabeli kujule:

Nüüd on vaja mõned read eemaldada – alguserida ja lehenumbrid. Selleks teen väikese abivahendi – veeru, kus neile ridadele, kus arv pannakse see arv (arvuna) ja mujale pannakse null ning eemaldan nii kõik read, kus arv < 1000 (lehenumbrid) arvud üle 1000 panen eraldi veergu ja täidan alla, nii saan igale väljaande aasta külge

Mängimist kui palju, aga toredam, kui 1000 raamatut sisse toksida

Nüüd otsin välja kõik read, mis algavad numbriga. Nimelt sealt tabelist tulevad mõned väljaanded ühe, mõned mitmerealisena ja need oleks vaja kokku saada. Ära tunnen ma nad selle järgi, kus alguses on number.

Sellest üksi ei piisanud – ühes väljaandes oli rea alguses 1870 – see oli paraku pealkirja osa, sellest sain ka ikkagi jagu

Ülearused veerud minema, mõned lisaarvutused ja tükeldus. Nii saan pealkirjade eest väljaande numbri kätte.

veel paar teisendust ja mul tabel, kus pealkirjad (mitmel real), aasta ja väljaanne. Lisaks tegin vaal paar kavalat teisendust, et pärast poolitusmärgid kätte saada ja eemaldada

Järgmiseks tegin grupeerimistehte, kus pealkirja read ilusti üheks sain.

Sealt pealkirjade eest on nüüd vaja need numbrid ära saada – see on lihtne, järgmine on aga keeruline vigur. Osa väljaandeid on autoriga, osa ilma. Õnneks on selle LRBiblio koostaja taibanud pealkirjad kirjutada üleni suurte tähtedega. Selle järgi tunnen ära ja tegutsen. Ei hakka iga sammu selgitama, aga tulemus sai selline:

Edasi uurisin natuke pealkirja järel olevat kommentaari – seal on variante kogunud, kogunud ja tõlkinud, kui tõlkinud, siis mis keelest. Mõni keel on ühesõnaline, mõni aga kahe-. Neid variante teisendades sain lõpuks tabeli, kus nii koguja, tõlkija, mis keelest jms

Ja selle tabeli siis laadisin juba Excelisse ja töö tehtud.

Oli palju lõbusam, kui 1000 nimetust käsitsi tabelisse kanda.

ja kui veel mõni tahab ise ka proovida, siis siin on kogu selle tegevuse käigus “iseenesest” tekkinud programmi- või päringukood. Seda ilusamaks ei viitsinud tegema hakata, kuigi oleks võinud:

// http://www.loominguraamatukogu.ee/wp-content/uploads/2017/02/LRbiblio.pdf

let
//    Source = Pdf.Tables(File.Contents("D:\OneDrive\ExtendedKodu\SQL Books\Free-Ebook\LRbiblio.pdf"), [Implementation="1.3"]),
Source = Pdf.Tables(Web.Contents("http://www.loominguraamatukogu.ee/wp-content/uploads/2017/02/LRbiblio.pdf"), [Implementation="1.3"]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Page")),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","Page","",Replacer.ReplaceText,{"Id"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Id", type number}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each [Id] >= 93 and [Id]  1000 or [Custom] = null),
#"Filled Down" = Table.FillDown(#"Filtered Rows2",{"Custom"}),
#"Filtered Rows3" = Table.SelectRows(#"Filled Down", each [#"Custom - Copy"] <> [#"Custom"]),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows3",{"Custom - Copy"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Aasta"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each try Number.FromText(Text.Middle([Data.Column1],0,1)) otherwise null),
#"Added Conditional Column2" = Table.AddColumn(#"Added Custom", "Custom.1", each if Text.StartsWith([Data.Column1], "1870") then null else [Custom]),
#"Removed Columns4" = Table.RemoveColumns(#"Added Conditional Column2",{"Custom"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns4",{{"Custom.1", "Custom"}}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns1", "Data.Column1", "Data.Column1 - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column1", "Data.Column1 - Copy", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Data.Column1 - Copy.1", "Data.Column1 - Copy.2"}),
#"Removed Columns2" = Table.RemoveColumns(#"Split Column by Delimiter",{"Data.Column1 - Copy.2"}),
#"Added Conditional Column1" = Table.AddColumn(#"Removed Columns2", "Custom.1", each if [Custom] = null then null else [#"Data.Column1 - Copy.1"] ),
#"Removed Columns3" = Table.RemoveColumns(#"Added Conditional Column1",{"Custom", "Data.Column1 - Copy.1"}),
#"Filled Down1" = Table.FillDown(#"Removed Columns3",{"Custom.1"}),
#"Added Custom1" = Table.AddColumn(#"Filled Down1", "Custom", each [Data.Column1]&"XXXXXX"),
#"Replaced Value1" = Table.ReplaceValue(#"Added Custom1","-XXXXXX","yyyyyy",Replacer.ReplaceText,{"Custom"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","yyyyyy","",Replacer.ReplaceText,{"Custom"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","XXXXXX"," ",Replacer.ReplaceText,{"Custom"}),
#"Removed Columns5" = Table.RemoveColumns(#"Replaced Value3",{"Data.Column1"}),
#"Grouped Rows" = Table.Group(#"Removed Columns5", {"Aasta", "Custom.1"}, {{"Koos", each Text.Combine([Custom]), type nullable text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Grouped Rows", "Koos", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Koos.1", "Koos.2"}),
#"Removed Columns6" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Koos.1"}),
#"Added Conditional Column3" = Table.AddColumn(#"Removed Columns6", "Custom", each if Text.At([Koos.2],1) = Text.Upper(Text.At([Koos.2],1)) and Text.At([Koos.2],1) <> "." then ". "&[Koos.2] else [Koos.2]),
#"Removed Columns7" = Table.RemoveColumns(#"Added Conditional Column3",{"Koos.2"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns7",{{"Custom.1", "Number"}}),
#"Added Conditional Column11" = Table.AddColumn(#"Renamed Columns2", "Custom.1", each if Text.Middle([Custom],4,2) = ". " then Text.ReplaceRange([Custom],4,2, "@") else [Custom]),
#"Added Custom2" = Table.AddColumn(#"Added Conditional Column11", "Custom.2", each if Text.Middle([Custom.1],1,2) = ". " then Text.ReplaceRange([Custom.1],1,2, "@") else [Custom.1]),
#"Removed Columns12" = Table.RemoveColumns(#"Added Custom2",{"Custom", "Custom.1"}),
#"Renamed Columns7" = Table.RenameColumns(#"Removed Columns12",{{"Custom.2", "Custom"}}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Renamed Columns7", "Custom", Splitter.SplitTextByEachDelimiter({". "}, QuoteStyle.Csv, false), {"Custom.1", "Custom.2"}),
#"Renamed Columns3" = Table.RenameColumns(#"Split Column by Delimiter2",{{"Custom.1", "Autor"}, {"Custom.2", "Nimetus ja märkus"}}),
#"Replaced Value8" = Table.ReplaceValue(#"Renamed Columns3","Valinud ja ","Valinud£ja£",Replacer.ReplaceText,{"Nimetus ja märkus"}),
#"Replaced Value16" = Table.ReplaceValue(#"Replaced Value8","@",". ",Replacer.ReplaceText,{"Autor"}),
#"Replaced Value9" = Table.ReplaceValue(#"Replaced Value16","Klassikalisest ","Klassikalisest£",Replacer.ReplaceText,{"Nimetus ja märkus"}),
#"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9","(I) ","(I)£",Replacer.ReplaceText,{"Nimetus ja märkus"}),
#"Added Conditional Column4" = Table.AddColumn(#"Replaced Value10", "Custom", each if Text.Contains([Nimetus ja märkus], "keelest") then [Nimetus ja märkus] else null),
#"Split Column by Delimiter3" = Table.SplitColumn(#"Added Conditional Column4", "Custom", Splitter.SplitTextByEachDelimiter({"keelest"}, QuoteStyle.Csv, false), {"Custom.1", "Custom.2"}),
#"Split Column by Delimiter4" = Table.SplitColumn(#"Split Column by Delimiter3", "Custom.1", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"Custom.1.1", "Custom.1.2"}),
#"Added Conditional Column5" = Table.AddColumn(#"Split Column by Delimiter4", "Nimetus1", each if [Custom.1.1] = null then [Nimetus ja märkus] else [Custom.1.1]),
#"Removed Columns8" = Table.RemoveColumns(#"Added Conditional Column5",{"Nimetus ja märkus"}),
#"Added Conditional Column6" = Table.AddColumn(#"Removed Columns8", "Custom", each if [Custom.1.1] = null then null else [Custom.1.2 ] & " keelest " & [Custom.2]),
#"Removed Columns9" = Table.RemoveColumns(#"Added Conditional Column6",{"Custom.1.1", "Custom.1.2", "Custom.2"}),
#"Renamed Columns4" = Table.RenameColumns(#"Removed Columns9",{{"Custom", "Tõlkija"}}),
#"Added Conditional Column7" = Table.AddColumn(#"Renamed Columns4", "Custom", each if Text.Contains([Nimetus1], "Koostanud") then [Nimetus1] else null),
#"Split Column by Delimiter5" = Table.SplitColumn(#"Added Conditional Column7", "Custom", Splitter.SplitTextByDelimiter("Koostanud", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
#"Added Conditional Column8" = Table.AddColumn(#"Split Column by Delimiter5", "Koostaja", each if [Custom.2] = null then null else "Koostaja " & [Custom.2]),
#"Replaced Value11" = Table.ReplaceValue(#"Added Conditional Column8","(I)£","",Replacer.ReplaceText,{"Tõlkija"}),
#"Added Conditional Column9" = Table.AddColumn(#"Replaced Value11", "Nimetus", each if [Custom.1] = null then [Nimetus1] else [Custom.1]),
#"Removed Columns10" = Table.RemoveColumns(#"Added Conditional Column9",{"Custom.1", "Custom.2"}),
#"Added Conditional Column10" = Table.AddColumn(#"Removed Columns10", "Märkus", each if [Tõlkija] = null then [Koostaja] else [Tõlkija]),
#"Removed Columns11" = Table.RemoveColumns(#"Added Conditional Column10",{"Nimetus1"}),
#"Replaced Value12" = Table.ReplaceValue(#"Removed Columns11","£"," ",Replacer.ReplaceText,{"Märkus"}),
#"Reordered Columns" = Table.ReorderColumns(#"Replaced Value12",{"Aasta", "Number", "Autor", "Nimetus", "Märkus", "Tõlkija", "Koostaja"}),
#"Trimmed Text" = Table.TransformColumns(#"Reordered Columns",{{"Märkus", Text.Trim, type text}, {"Tõlkija", Text.Trim, type text}, {"Koostaja", Text.Trim, type text}}),
#"Duplicated Column2" = Table.DuplicateColumn(#"Trimmed Text", "Tõlkija", "Tõlkija - Copy"),
#"Split Column by Delimiter6" = Table.SplitColumn(#"Duplicated Column2", "Tõlkija - Copy", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Tõlkija - Copy.1", "Tõlkija - Copy.2"}),
#"Renamed Columns5" = Table.RenameColumns(#"Split Column by Delimiter6",{{"Tõlkija - Copy.1", "Keel"}}),
#"Split Column by Delimiter7" = Table.SplitColumn(#"Renamed Columns5", "Tõlkija - Copy.2", Splitter.SplitTextByDelimiter("keelest", QuoteStyle.Csv), {"Tõlkija - Copy.2.1", "Tõlkija - Copy.2.2"}),
#"Renamed Columns6" = Table.RenameColumns(#"Split Column by Delimiter7",{{"Tõlkija - Copy.2.1", "Keel2"}, {"Tõlkija - Copy.2.2", "Tõlkija nimi"}}),
#"Trimmed Text1" = Table.TransformColumns(#"Renamed Columns6",{{"Keel2", Text.Trim, type text}, {"Tõlkija nimi", Text.Trim, type text}}),
#"Replaced Value4" = Table.ReplaceValue(#"Trimmed Text1",". ","#",Replacer.ReplaceText,{"Tõlkija nimi"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4",".","",Replacer.ReplaceText,{"Tõlkija nimi"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","#",". ",Replacer.ReplaceText,{"Tõlkija nimi"}),
#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","",null,Replacer.ReplaceValue,{"Keel2"}),
#"Replaced Value13" = Table.ReplaceValue(#"Replaced Value7","Valinud£ja£","",Replacer.ReplaceText,{"Keel"}),
#"Replaced Value14" = Table.ReplaceValue(#"Replaced Value13","£"," ",Replacer.ReplaceText,{"Keel"}),
#"Replaced Value15" = Table.ReplaceValue(#"Replaced Value14","ja ","",Replacer.ReplaceText,{"Keel2"}),
#"Trimmed Text2" = Table.TransformColumns(#"Replaced Value15",{{"Keel", Text.Trim, type text}, {"Keel2", Text.Trim, type text}}),
#"Lowercased Text" = Table.TransformColumns(#"Trimmed Text2",{{"Keel", Text.Lower, type text}, {"Keel2", Text.Lower, type text}})
in
#"Lowercased Text"

Kood töötab igas masinas. Ma tegin selle küll allalaetud faili peal, aga päringu teisendasin onlinest võetava PDFi tarbeks.

Rõhutan, et nii ei pea tegema, aga võib

Uus funktsioon Excelis =LAMBDA

Eelmine tore vigur Excelis oli =LET, nüüd siis uus tegelane =LAMBDA

Vaatame lähemalt millega tegu. Meenutuseks, mis see =LET oli

 =LET(
 x;7;
 y;8;
 x*x+y*y
) 

Niisugune avaldis Excelis annab vastuseks 113 (s.o 49 + 64)

Asja mõte on defineerida kaks muutujat ja siis nendest avaldis. Kui nüüd 7 ja 8 asemel on mingid viited – näiteks A1 ja A2, saab keerulise ja kordustega avaldise kirjutada lühemalt ja lihtsamini. Kusjuures muutujate arv ei ole piiratud ja kord defineeritud muutjat saab juba järgmiste defineerimisel kasutada. Nii mõnegi keelega sarnaseid konstruktsioone saab nii teha.

Kirjutasin sellest oma ühes varasemas postituses.

Vaatame nüüd, mis asi see =LAMBDA on

 =LAMBDA(
 x;
 y;
 x*x+y*y
)
(7;8) 

Ka see annab samasuguse tulemuse – ei midagi erilist. Lihtsalt muutujate väärtusavaldised on tõstetud sulgude taha.

Aga teeme väikese täienduse ja paneme selle valemi nimede hulka (Formula – Names – Name Manager). Anname talle nime RUUTUDESUMMA (teeme lühemalt RSUM) ja jätame selle sulgudetaguse osa ära.

ja nüüd proovime kuskil Exceli lehel:

Nagu näha – =LAMBDA defineeritud nimega võimaldab meil luua Excelis oma funktsioone ja neid seal Excelis siis ka kasutada. Asi millest ammu unistada pole tohtinud ilma seda jubedat VBA-d kasutamata

See loob uusi väga põnevaid võimalusi – teeme paar näidet

=LAMBDA(
 _lat1;_lon1;_lat2;_lon2;
 LET(
      lat_1; RADIANS(_lat1);
      lon_1; RADIANS(_lon1);
      lat_2; RADIANS(_lat2);
      lon_2; RADIANS(_lon2);
      r; 6378;      
      ACOS((SIN(lat_1)*SIN(lat_2))+(COS(lat_1)*COS(lat_2)*COS(lon_2-lon_1))) * r ))

Selle avaldise panen nimega KAUGUS defineeritud nimede hulka. See peaks maakeral kahe punkti kauguse andma linnulennult – need latid ja longid on pikkus- ja laiuskraadid. Siin on arvutuse lihtsustamiseks =LAMBDA sees omakorda =LET-i kasutatud. See 6378 peaks maakera raadius olema.

Nüüd oleks kuskilt vaja leida need latid ja longid

Excelis on õnneks vahva andmetüüp – Linn (tegelikult geograafiline koht) ja sellel on nii latid kui longid olemas

Kirjuta lahtrisse Tallinn ja vajuta data ribal Geography märki

Edasi sealt ikoonikese alt leiad nii latid kui longid

Need pannakse valemina ilusti kirjaja neid saab ka teiste linnade puhul kasutada

oma elu lihtsustamiseks ma teen veel ühe ‘funktsiooni’ (nimega avaldise) LINNADEKAUGUS, mis kasutab seda eelmist

=LAMBDA(
   city1;
   city2; 
   LET(
      lat_1; city1.Latitude;
      lon_1; city1.Longitude;
      lat_2; city2.Latitude;
      lon_2; city2.Longitude;
      distance; KAUGUS(lat_1; lon_1; lat_2; lon_2);
      IFERROR(distance; "an error occurred") ))

See funktsioon võtab parameetriks kaks linna, leiab nende latid-longid ja arvutab kauguse.

Proovime – esialgu kahe linnaga: Tallinna ja Tartuga

Teeme nüüd pisut keerukama töö

Toksin Excelisse mõned Eesti linnad (vabandan Mõisaküla, Viljandi, Rapla, Keila ja veel mõne linna ees – ei tahtunud pilti väga kribuks ajada)

Teen nad ‘linnadeks’ (geography tüüp)

Ja siis lisan lehele paar valemit:

JA toimibki

Täna rohkem ei jutusta – sina imetle. Kui see LAMBDA jõuab kõigini (praegu ainult minusuguste uudishimulike e insiderite käes), siis proovi ja sa ei jõua ära imestada, mida saab ühe väikese uue funktsiooniga ära teha.

Ja kui sa nüüd küsid, miks seal C6 valemis C5# on, siis see on juba teise korra jutt, aga peaasi et toimib).

Juhtum viiruse ja statistikaga

Detsember

Sellenädalane osakonna koosolek erines paljuski tavalisest. Professor oli kutsunud mitu külalist. Ka meid kõiki palus ta eraldi igaüht kindlasti osaleda. Koosoleku põhiteema oli sellist kutsumist väärt – muidu oleks vist minemata jätnud. Ikka ja jälle „Nakatumiste statistiline analüüs ja prognoosimine“. Ette pidid kandma meie ’põhistatistikud’ – Marje ja Mare.

Marje ja Mare, kaks tüdrukut (võin ju 30-aastaste kohta tüdruk öelda) olid me osakonnas juba paar aastat ja ei tegelnud laboris kolbide ega mikroskoopidega nagu teised. Nad istusid omaette toas ja kogu aeg arvuti taga. Rõõmsad tüdrukud, rääkisid aegajalt nii temaatilisi kui mittetemaatilisi nalju, kui kohvikus juttu ajama juhtusime. Kohvikus olid nad tihti – neile meeldis kohvi juua ja ’arvuti tagant korraks välja saada’. Kolleegidele nad meeldisid kuigi keegi päris aru ei saanud, mida nad teevad.

Nagu ma aru sain, oli nende tööks numbreid kokku liita või teisisõnu statistikat teha. Viiruste, haigete, nakatunute ja mis iganes. Peamiselt kajastus nende töös see, et jälle on nakatunute ja nakatumisriski tõenäosus tõusnud. Nad ikka ennustasid järgmise nädala numbreid ja arutasime, kas ja kui palju ennustused paika pidasid. Enamasti pidasid. Kevadisest ’esimesest lainest’ saadik olid nad üsna täpset tööd teinud.

Tavaliselt oli nende osa koosolekust lühike, asjalik ja kokkuvõttes igav – numbrite kasvu sai igast lehest lugeda. Nii ei mõistnud ei mina ega mõnedki kolleegid (nägudelt näha) tänase pidulikkuse vajalikkust. Kõik kohale ja külalised ka.

Marje alustas nagu ikka, Mare sekundeeris arvuti tagant pilti näidata. Neil oli miski oma programm, mis ilusaid pilte ja jooniseid tegi ja neile meeldis neid näidata ja juurde rääkida. See tüdrukutepaar valdas nii teemat kui tehnikat ja nad suutsid alati sujuvalt – üks rääkis, teine näitas – esineda. Nii mõnigi teine kolleeg oli alati hädas kas siis slaididega või tehnikaga või mõlemaga. Neil oli see ’värk’ käpas.

„Täna näitame teile paari trendijoont, mis meid hämmastanud on,“ tutvustas Marje. „Muud numbrid muutuvad täiesti normaalselt, aga nakatumistõenäosus rahvuste lõikes hakkas meile kummaliselt silma.“

„Kas te uurite nakatumist rahvuste lõikes?“ segas vahele üks külalistest – viroloogiaprofessor teisest osakonnast. Ta oli selline pisut kitsa näoga vanem härra, rahvuselt vist leedulane või poolakas. Ta ikka vahel käis meil külas, esines väga karmilt ja pisut peenikese häälega ja rääkis alati viirustest. Omavahel kutsusime teda viirusekandjaks.

Mare aitas arvuti tagant: „ei me ei uuri asju mingis lõikes. Me laseme oma mudelil ennast ise ehitada. Anname talle igasuguseid andmeid, et saaks mingeidki tunnuseid, mis võiks huvi pakkuda. Oleme saanud nakatunute andmetele lisada andmed rahvastikuregistrist, ootame pingsalt, et saaks loa andmete sidumiseks geenipanga  andmetega. Meie eesmärk on lasta mudelil leida mingeid tunnuseid, mis võiks liigitada nakatunuid ja leiame selle najal tõenäosusi homseks.“

„Tunnuseid, mille järgi nakatunuid ja ka kontaktseid mittenakatunuid liigitada, valib mudel ise,“ jätkas nüüd Marje. „Meie algne idee oli uurida, kas toimetulek, eluviisid, elamiskoht, sissetulek või mõni muu tunnus võiks korreleeruda ja just nakatumistõenäosusega. Ja meil on ka mõningaid tunnuseid, mis käituvad täitsa normaalselt.“

Edasisest jutust siis selgus, et viimased paar nädalat on hakanud ’pead tõstma’ rahvuslik jaotus. EI kevadel, esimese laine ajal, ei suvisel pausi ajal ega ka sügisel teise laine alguses olnud see tunnus kuidagi välja paistnud ja nüüd – paar viimast nädalat, umbes detsembri algusest, on arvudesse tekkinud mingi müstika. Eestlased.

Eestlaste nakatumistõenäosus on hakanud silmatorkavalt langema. „Pidasime seda alguses statistiliseks anomaaliaks,“ kinnitasid tüdrukud, „aga asi on hakanud nädal nädalalt süvenema ja seda järsult.“

Selgub nüüd, et eestlased on saamas jõulukingitust. Detsembri kaks nädalat on eestlaste, ja just ainult eestlaste, nakatumistõenäosus langenud optimistlikult nulli poole. Tõsi, pisut ka teistel rahvustel – pisut venelastel ja lätlastel. Ega meil teisi rahvuseid väga valimis ei olegi. Üksikud.

Oleme proovinud saada naabritelt nakatumisdemograafiat, aga ei ole julgenud rahvuse peale rõhuda ja kellelgi ka pole selliseid andmeid või ei taha nad neid jagada. Praeguse pildiga ei julgeks ka ise teistele maadele infot jagada.

Vaatasime graafikut ja pilt rääkis ise enda eest. See oli selgemast selgem. Kui see pole välja mõeldud, on tegu tõesti mingi seletamatu rahvusliku müstikaga.

Juttu täiendas prof. osakonnajuhataja (nii me teda kutsusime): „statistikud tutvustasid seda leidu mulle juba nädalapäevad tagasi. Ei suutnud ka mina seda alguses tõsiselt võtta, ootasime mõned päevad ja jälgisime mustrit. Selle süvenedes pidasin vajalikuks teemaga laiemas ringis tutvuda ja mõelda, mis sellest võiks välja tulla.“

„Paneme näoraamatusse, saame kuulsaks,“ pakkus tehnik Jüri. Kõik vaatasid teda pikalt ja kurjalt, et viimane kinnitaks – see oli naljana mõeldud.

Üldine arvamus oli, et enne paari nädalat ei tohiks sellest laiemalt juttu teha. Uurime ja jälgime asja. On vähemalt endil põnev. Jõuluks peaks olema selge, kas tegu statistilise veaga või on siin mingi muu arusaamatus. Statistilise vea juures vaatasid tüdrukud üksteisele otsa ja muigasid. NO teadagi, statistikaga ja statistilise veaga saab kõike põhjendada.

Tüdrukud rõhutasid veelkord, et kummaline ei ole see rahvuslik anomaalia ise vaid selle ootamatu ajaline tekkimine. Ei kevadel, ei suvel ega sügisel. Alles nüüd – detsembris. Ja nii nähtav. Ise sellist asja ei oleks küll taibanud uurida.

Pühad

Jõulud läksid meie majas nagu ikka. Kes töötas, kes võttis pisut puhkust. Kaugele keegi seekord ei sõitnud – teadagi miks. Pikka puhkust samuti ei võtnud keegi – polnud mõtet ja oli ka põnevus. Olime kokku leppinud, et oma statistilise leiu jätame aasta lõpuni oma teada. Siis võtame juhtkonnaga arutada, kas ja kui palju ja kellele sellest rääkida.

Numbrid jätkasid langemist. Eestlaste nakatumistõenäosus oli langenud nii madalale, et võiks rääkida rahvuslikust immuunsusest. Tüdrukud olid kolm korda oma mudelit kontrollinud, ümber ehitanud (nii palju, kui see võimalik), testinud – tulemus ikka sama. Ja süvenev.

Ainus erinevus varasemaga oli, et kutsusime osakonda külaliseks ka paar asjameest arvutiteadustest. Rohkem me teadjate ringi ei laiendanud. Nood vaatasid koos tüdrukutega asja üle ja jäid nõutult nõusse – kõik õige.

Osakonna Jõulupeol me muust ei rääkinudki. Isegi kingitusi ei taibanud seekord teha. Sõime ja jututasime.

Jaanuari alguses tutvustasime oma leidu teadusnõukogus. Lugu tekitas elevust ja pani nii mõnegi muu valdkonna teadlase kukalt kratsima. Ei ole ju võimalik, et Hiinas loodud või tekkinud viirus mingi aeg valib ühe valitud rahva ja talle pehmemat poolt näitab.

Teadusnõukogu pidas nõu ja nõustus, et sellist tulemust maailmale kuulutada oleks jube ohtlik. See, mis sellele võib järgneda, on ennustamatu. Viiruse käitumist ja nakkamisi suudaks veel ennustada, aga inimeste, eriti rahvaste ja riikide, poliitikute ja teadjameeste käitumist ja arvamist ennustada – võimatu. Päris kalevi alla ei saa avastust lükata, aga jätame uurimise uurimisfaasi veel kuuks ajaks. Ja mis kõige olulisem – proovime nähtuse põhjusele jälile saada.

Nähtuse põhjus?

Elustiil või elamistingimused ei saanud need olla. Geneetika – keeruline, eestlased ei ole väga geneetiline konstruktsioon. Suhtlemisviis – vaiksed ja endassetõmbunud, ka see on pigem müüt.

Tööd ja mõtlemist igatahes kuhjaga.

Jaanuar

Jaanuar muutis meie pilgud kurvaks. Kui Jõuluks oli eestlaste nakatumistõenäosus peaaegu nulli jõudnud, siis umbes jaanuari teisest nädalast alarmeerisid tüdrukud meid uuest trendist. Tõenäosus hakkas tasapisi taas tõusma. Tundsime endid paljaksvarastatuna, aga lootsime juba koos, et statistiline viga. Selle kuu-pooleteisega oli kogu senine labori töökorraldus muutunud. Pea kõik me tundsime statistilisi meetodeid, mõisteid, töövahendeid ja piiranguid.

Meie tüdrukute ehitatud mudel oli selle poolest põnev, et kaasas nii nakatunud ja nende andmed, kui ka kontaktsed ja nende andmed. Kõik, keda mingil moel testiti aga ka neid, keda ei saanud või ei jõutud või ei peetud vajalikuks testida.

Igatahes tõenäosuskõvera pöördumine oli meie jaoks väga kurb. Elasime teemas kõik koos.

Jaanuari lõpuni jätkus kõvera tõus ja meie näod mornistusid järjest.

Veebruaris tõus jätkus ja Vabariigi aastapäevaks oli peaaegu samasugune nagu teistelgi. Kaks kolm kuud optimismi ja elevust kui peoga pühitud.

Ühest olime vähemalt tänulikud – et me oma osakonnas leidsime ja et ka teadusnõukogus leiti, et ei maksa maailmale kuulutada. Hea, et see periood vaid meie teada jäi. Ei oleks tahtnud nüüd selgitada ja seletada …

Lahendus

Lahendus jäigi tulemata? Ei jäänud.

Meie jaoks oli endiselt üleval küsimus, millest selline asi tingitud oli. Mis põhjustas jooksvas statistikas sellise moonutuse. Paljud pöördusid statistilise vea usku, aga tegelikult oli vaja mingit selgust.

Kas sellist kõverdust võib veel ette tulla?

Kas see on seotud kindla ajaga – Jõulust Vabariigi aastapäeva?

Kas see on seotud kindla rahvusega? Ja kuidas just rahvusega?

Mis veel maailmas ja eestlaste elukorralduses sellel ajal teistmoodi oli, mis võinuks kogu loo põhjustada.

Kui sama lugu korduma peaks – annaks Jumal, et seda statistikat aastateks ei jaguks – kas annaks kuidagi seda kõvera pöördumist peatada? Mõni teine rahvas kaasata.

Uurisime pisut laiemalt, mis ja kuidas. Tänu teadusnõukogule oli võimalik ka teistel lasta uurida perioodi detsember-veebruar, et mis nähtused veel samale kõverale võiks vastata. Mis iganes eluvaldkond. Ei hakanud me jagama teemat kõigiga – andsime ette tüdrukute saadud kõvera ja palusime uurida, mis võiks selle kõveraga sarnaselt käituda. Ehk andsime küsimuse, mille graafik see võiks olla.

Ja lõpuks kostis päikesevalgus. Kas ja kuidas seda ära kasutada – see tõstab uued küsimused, aga mingi tulemus ikkagi.

Meie tõenäosuse kõverusega kattus ainsana kõigist muudest eluvaldkondadest ainult üks.

See oli verivorst. Verivorsti müük ja selle kasutamisstatistika. Just see eestlaste veidrus osutus ainukeseks mõeldavaks kandidaadiks meie statistilise kõvera põhjendamiseks.

Nii me arvasime ja sellisele veendumusele jäime.

Sest õnneks seda teooriat kontrollida enam ei saanud.

Ja ega ei tea, mis oleks juhtunud, kui oleksime proovinud kogu maailma õpetada Eesti Verivorsti sööma.

Pohlamoosiga.

NB! Kõik siinsed nimed ja juhtumised on väljamõeldis. Võimalikud sarnasused tegeliku eluga on statistiline viga.  

Isikukoodi valideerimine – uued valemid Excelis

Mõned ajad tagasi kirjutasin väikese loo, kuidas excelis valideeerida isikukoodi. Tookord panin kokku üsna keerulise valemi, millest pärast ise ka aru ei saa:

=IF(ISERROR(TEXT((CODE(MID("FEDCA@";LEFT(A11;1);1))-50)*1000000+LEFT(A11;7);"0000\.00\.00")+0);"isikukood vigane";
IF(IF(MOD(SUMPRODUCT((MID(A11;COLUMN($A$1:$J$1);1)+0);
(MID("1234567891";COLUMN($A$1:$J$1);1)+0));11)=10;
MOD(MOD(SUMPRODUCT((MID(A11;COLUMN($A$1:$J$1);1)+0);(MID("3456789123";COLUMN($A$1:$J$1);1)+0));11);10);
MOD(SUMPRODUCT((MID(A11;COLUMN($A$1:$J$1);1)+0);(MID("1234567891";COLUMN($A$1:$J$1);1)+0));11))=MID(A11;11;1)+0;"isikukood õige";"kontrolljärk vale"))

(tookordne valem, reavahedega, et oleks lihtsam lugeda)

Vahepeal on Excelisse lisandunud suur hulk uusi funktsioone ja ka keerulisi asju saab palju lihtsamaks teha. Seepärast lisan siia sama valemi uuemal kujul ja siis pisut selgitan lahti

=LET(
x;A11;
jada10;SEQUENCE(10);
jada10x;MID(x;jada10;1)+0;
jada10s;{1;2;3;4;5;6;7;8;9;1};
jada10a;{3;4;5;6;7;8;9;1;2;3};
jadaS;MOD(SUMPRODUCT(jada10x;jada10s);11);
jadaA;MOD(SUMPRODUCT(jada10x;jada10a);11);
KS;IF(jadaS=10;MOD(jadaA;10);jadaS)&"";
IF(ISERROR(TEXT(CHOOSE(LEFT(x;1);21;21;21;21;20;20)&MID(x;2;6);"0000\.00\.00")+0);"isikukood vigane";IFERROR(IF(KS=MID(x;11;1);"isikukood õige";"kontrolljärk vale");"pole isikukood")))

(jälle reavahedega, et oleks lihtsam lugeda)

nüüd ma pean siis seletama mõned uued vigurid, mida ekselis teha saab

* Funktsioon CHOOSE (seal viimasel real), millega asendasin selle veidravõika konstruktsiooni “FEDCA@”, millest keegi peale Möldri Tommi aru ei saanud
CHOOSE on lihtne võimalus, järjekorra numbri järgi (esimene parameeter) valida jadast õige väärtus (ülejäänud parameetrid)
Nii kasutangiseda, et isikukoodi esimese numbri järgi valida sajand (18, 19 või 20). MIks ma 19 asemel aga 21 võtan, on lihtne kokkulepitud viga kuupäevades – nimelt kunagi eksikombel jäeti Excelisse ja teistesse tabelarvutustesse sisse viga nagu oleks aasta 1900 liigaasta olnud, korrektsuse mõttes asendan siin kuupäeva kontrollimiseks 20. sajandi 22. sajandiga, kus seda viga ei ole (enam). Samuti asendan 18 21-ga, sest Excel ei toeta kuupäevi enne 1900 aastat.

* teine uus kaval funktsioon SEQUENCE millega asendasin keeruka COLUMN($A1:@J1). Sequence nimelt võiamldab valemites kasutada arvujadasid ja SEQUENCE(10) teeb mulle sobiva jada 1,2,3…10

* viimane uus funktsioon (kõigile ei ole see veel uuendusena jõudnud) on LET. MInu jaoks muutis see Exceli valemite kirjutamise täiesti uueks. NImelt võimaldab LT defineerida valemi sees muutujaid (või noh mis nad ikka nii väga muutuvad) – nimesid, millele saab anda arvutusliku väärtuse. Need kehtivad ühe valemi piires ja neid võib üle 10 niimoodi tekitada. See võtab ära vajaduse korrata valemis sama arvutust mitu korda ja muuta valemi kompaktsemaks ja loetavamaks (vist?).

Läheme veelkord selle valemi kallale ja ma siis lisan kommentaarid (päris valemis kahjuks kommentaare olla ei saa) asja selgitamiseks.

=LET(                                           // defineerin hakatuseks mõned 'muutujad'
x;A11;                                          // x - las see olla lahter, milles peidab end isikukood. Nüüd on hea valmit muuta, asendades A11 ühes kohas
jada10;SEQUENCE(10);                            // jada10 - teen omale jada 1..10, seda läheb allpool paar korda vaja 
jada10x;MID(x;jada10;1)+0;                      // jada10x - muudan isikukoodi arvude jadaks
jada10s;{1;2;3;4;5;6;7;8;9;1};                  // jada10s - lisan veel ühe arvude jada, kontrolljärgu kaaludega
jada10a;{3;4;5;6;7;8;9;1;2;3};                  // jada10a - ja veel ühe - kontrolljärgu alternatiivkaaludega
jadaS;MOD(SUMPRODUCT(jada10x;jada10s);11);      // jadaS   - leian kontrollsumma (korrutan kaks jada, liida ja leian jäägi 11-ga)
jadaA;MOD(SUMPRODUCT(jada10x;jada10a);11);      // jadaA   - leian ka alternatiivse kontrollsumma (kui seda pole vaja, siis seda ka ei arvutata)
KS;IF(jadaS=10;MOD(jadaA;10);jadaS)&"";         // KS      - ja arvutan lõpliku kontrollsumma, kui esimene on 10, siis võtan alternatiivse, muidu käib see esimene küll
                                                // edasi siis kuupäevakontroll - umbes samuti, kui eelmisel korral ja siis kontrollsumma võrdlus IK viimase märgiga
IF(ISERROR(TEXT(CHOOSE(LEFT(x;1);21;21;21;21;20;20)&MID(x;2;6);"0000\.00\.00")+0);"isikukood vigane";IFERROR(IF(KS=MID(x;11;1);"isikukood õige";"kontrolljärk vale");"pole isikukood")))

Nonii – ma ei tea, kuidas sinul, hea lugeja, aga minu jaoks muutus see valem kordades lihtsamaks ja nüüd oskaks ma kiirelt panna kokku ka pangakonto või viitenumbri kontrolljärgu arvutamise ja kontrollimise.

Kuidas saada kõikse uuem Excel 2016

Excel (kiiremini kui teised Office’i tükid) areneb viimasel ajal suurte sammudega, kahjuks ei jõua need kohe kõigi kasutajateni. Minul niinimetatud “insiderina” on palju väga vahvaid võimalusi ja heal meelel jagaks teistega ka.

Insideriks ei saada jumala armust – insideriks võib täiesti ise hakata – see ei nõua muud, kui tahtmist ja viitsimist. Tahtmist korra ja viitsimist nii veerand kuni kolmveerand tundi.

Paraku saavad seda endale probleemita lubada need, kel oma arvuti üle voli (kel on isiklik arvuti) ja kel Office 365 (kas siis isiklik või ettevõtte oma). Office 365 ja Office 2016 on suht sarnased asjad, aga just uuenduste ja litsensinduse osas on erinevusi. Samas kõiki ettetulevaid probleeme saab hõlpsasti murda (erijuhtusid saan selgitada eraldi ja kirja teel).

Hakatuseks on hea kaeda, mis Office sul on. Selleks ava mõni Word või Excel, mine File menusse (täpsemalt backstage lehele), vali sealt konto (account). Seal on kirjas, milline Office sul on:

Järgmiseks sammuks tuleks omale hankida Office’i paigaldaja (Office Deployment Tool).

https://www.microsoft.com/en-us/download/details.aspx?id=49117

Just see on aadress, kust selle kõik-see lihtsamini saab, aga saab ka veebist otsides. Laadid alla ja paigaldad. Küsib kuhu, pakud talle kuskile uue kausta. Mina tegin D-kettale kausta OfficeTool2016. Sinna paigaldatakse neli faili:

Nüüd pead otsustama, millist Office’it sa vajad – 64bit või 32bit. Kõik soovitavad 32, mina kasutan 64. Arutelu, kumb on parem, ei vii kuhugi – järgmistes kaob 32 bit nii-ehk-nii ära. Kui sa valid 64bit, siis edasi keskendud failile configuration-Office365-x64.xml, kui valid 32bit, siis failile configuration-Office365-x86.xml (ära küsi, miks neil nii veidrad nimed). NB! JA ära taha omale Office2019 – see fail unusta ära (kustuta maha)

Võta oma valitud fail ette miski tekstieditoriga (näiteks Notepad või mõni käepärane. Igatahes mitte Word 🙂

See faili sisu peaks välja nägema umbes selline (vajadusel siis korrigeeri):

NB! see roheline osa (mul on ta roheline, kuna kasutasin notepad++) ei ole oluline, selle võib ka maha kustutada. Olulised on read, kus OfficeEdition=”64″ või “32” ja Channel=”InsiderFast” (vt viimane on kahes kohas). Minul on siin Office nii eesti kui inglise keeles, soovi korral võid keeli lisada. Kõigi nende ridade struktuur peab samaks jääma.

Kui see tehtud, siis salvesta fail sinnasamma, kus ta oli.

Edasi läheb keeruliseks (vajadusel kasuta tuttava itipoisi abi). On vaja avada käsurida (Win-R klahv siis CMD ja siis Enter) ning liikuda sinnasamasse kausta (CD käsk):

Seejärel tuleb käsurealt öelda:

setup /download configuration-Office365-x64.xml

seega selle faili nimi, mis sa valisid. Peale seda mine tee omale kohvi või võileiba või vaata telekat. Arvuti umbes 15-30 minutit ei tee midagi (sõltuvalt võrgu kiirusest). Aga kui ta lõpetab, on sul seal kaustas Office’i paigaldus – sinna tekib alamkaust Office, kus on hästi palju kõiksugu pudinaid (minul selle kausta suurus kuskil pisut alla 3 GB, aga mul on seal ka Project ja Visio).

Nüüd on aega hinge tõmmata ja kuulata, mis ma räägin.

• Esiteks – on viimane aeg alla anda
• Teiseks – on veel viimane aeg alla anda
• Kolmandaks – on vaja kindel olla, et sa tead, mida sa teed
• Neljandaks – on vaja veenduda, et kui untsu läheb, oskad sa välja vingerdada

Kuigi midagi untsu minna ei saa, siis järgmine samm on meil, et me korjame Office’i oma arvutist ära. Sa pead:

• seda oskama
• ja kui katki läheb, siis vana tagasi panna

Kui sul on isiklik Office Home and Student – ära edasi tee. Küsi minult nõu.

Kui sul on ettevõtte arvuti, ettevõtte volume licensiga Office 2016 – ära edasi tee. Küsi oma ettevõtte itimehelt nõu.

Järgmine samm – eemalda arvutist (kui seal on) praegune Office. Control Panel, PC Settings – mis iganes kaudu.

Kui see tehtud (igaks juhuks kontrolli), siis paigalda uuem ja uuenev Office, selleks sealsamas kaustas, kus enne, sellelt samalt käsurealt (võid uuesti avada), käivita:

setup /configure configuration-Office365-x64.xml

Seal jälle seesama fail, millest juttu oli (minul 64-bitine, kui sul 32-bitine, siis x86)

Office’i paigaldamine läheb käima, saad omale viimase (kõige värskema) versiooni ja hakkad saama kord nädalas (umbes) uuendusi. Ja ei jõua ära imestada.

Ja kui jänni jääd või nõu tahad küsida, siis küsi julgesti Hennult või Sarviktaadilt

Järgmises loos siis, et mida on Excelis uut.

 

 

 

 

Triipkood Code128 ja Excel

Hiljuti oli mul vaja kasutada triipkoodi ühes Exceli rakenduses. No et kirjutan või arvutan lahtrisse miski numbri, ja see ilmub kuhugi triipkoodina, et siis seda kunagi hää oleks ekraanilt või paberilt skänneriga lugeda

Midagi sellist:

Parim, mis ma suutsin leida, oli miski 100$ maksev lisajubin Excelile ja Wordile. Aga nagu ikka, raha pole. Hakkasin siis leiutama

Esiteks – leidsin Code128 fondi – täitsa tasuta:

http://www.dafont.com/code-128.font

Seejärel uurisin pisut seda, kuna sinna tuleb lisada veel algus ja lõpp ning kontrollsumma, siis kirjutasin valemi:

="Ì
"&K30&CHAR(32+MOD(104+SUMPRODUCT(     CODE(MID($K$30;COLUMN(OFFSET($A$1;0;0;1;LEN($K$30)));1))-32;COLUMN(OFFSET($A$1;0;0;1;LEN($K$30))));103))&"Î
"

Tunnistan, valem on keeruline, aga tulemust väärt (kokkuhoid 100$ per töökoht). Mõni sõna selgituseks ka:

• K30 selles valemis on see lahter, kus on teisendatav tekst – valemis 4 korda
• need katusega suured I-d seal alguses ja lõpus peavad olema ja koos reavahetusega (alt-enter)
• $A$1 peab valemis olema just $A$1

 

Peale mõningaid uuendusi tuleb kasutada pisut uuemat valemit – panin teksti lahtrisse K3 ja kasutan sellist valemit

="Ì
"&K3&CHAR(32+MOD(104+SUMPRODUCT(     CODE(MID(K3;SEQUENCE(;LEN(K3));1))-32;SEQUENCE(;LEN(K3)));103))&"Î
"

Kasuta omal riisikol 🙂

Tulemuselahtri tegin hästi suure (72p) ja joondasin kokku mitmest ning panin sellesse allalaetud ja installlitud fonti

 

 

Kalendridimensioon SQLis

Võib-olla ei peagi alati kalendriks vajalikku tabelit andmebaasi tegema.
Panin siin kirja lühikese päringu, mida saaks andmelaos kasutada kalendridimensiooni aseainena

Võid seda kasutada päringuna või salvestada view-na

Võid vabalt kasutada, muuta, täiendada ja kritiseerida

with numbrid as (
select top 3000  -- siia kirjuta kalendri pikkus
ROW_NUMBER() over (order by rand()) nr
, cast('20000101' as datetime) Algus   -- siia kirjuta kalendri algus
from sys.objects 
cross join sys.columns 
)
, kuupäevad as
(
select dateadd(day, nr-1, algus) as kp
from numbrid
),
pühad (k, p, Nimetus) as (
select 01,01, 'Uusaasta' union all
select 02,24, 'Vabariigi aastapäev' union all
select 05, 01, 'Kevadpüha' union all
select 06, 23, 'Võidupüha' union all
select 06, 24, 'Jaanipäev' union all
select 08, 20, 'Taasiseseisvuspäev' union all
select 12, 24, 'Jõululaupäev' union all
select 12, 25, 'Jõulu 1. püha' union all
select 12, 26, 'Jõulu 2. püha'
),
lihavõtted (lp) as
(
select '20000423' union all
select '20010415' union all
select '20020331' union all
select '20030420' union all
select '20040411' union all
select '20050327' union all
select '20060416' union all
select '20070408' union all
select '20080323' union all
select '20090412' union all
select '20100404' union all
select '20110424' union all
select '20120408' union all
select '20130331' union all
select '20140420' union all
select '20150405' union all
select '20160327' union all
select '20170416' union all
select '20180401' union all
select '20190421' union all
select '20200412' union all
select '20210404' union all
select '20220417' union all
select '20230409' union all
select '20240331' union all
select '20250420' union all
select '20260405' union all
select '20270328' union all
select '20280416' union all
select '20290401' union all
select '20300421' union all
select '20310413' union all
select '20320328' union all
select '20330417' union all
select '20340409' union all
select '20350325' union all
select '20360413' union all
select '20370405' union all
select '20380425' union all
select '20390410' union all
select '20400401' 
)
select kp 
, format(kp, 'yyyy\. a\.') as AastaNimi
, year(kp) as AastaNr
, format (kp , 'MMMM', 'et-ee') as KuuNimi
, month(kp) as KuuNr
, format(datepart(iso_week, kp), 'Nädal 00') as NädalNimi
, datepart(iso_week, kp) as NädalNr
, format (kp, 'dddd', 'et-ee') as Nädalapäev
, 1+(datediff(day, '19550307', kp) % 7) as NädalapäevNr
, coalesce(
	p.Nimetus, 
	case when l.lp is not null then 'Lihavõtted' end,
	case when r.lp is not null then 'Suur Reede' end
	, '') as PühaVõiTavaline
from kuupäevad k
left join pühad p on p.k = month(k.kp) and p.p = day(k.kp)
left join lihavõtted l on l.lp = k.kp
left join lihavõtted r on r.lp = DATEADD(day, 2, k.kp)

Piibel ja SQL

Kuidas ma SQL serveriga Piiblit lugesin

Töö ja õpingutega seoses oli mul tarvis ja on ka edaspidi vaja Piiblit lugeda. Ja mitte lihtsalt vaid pisut ka loetut analüüsida. Piinlik tunnistada, et aega süstemaatiliselt ja mõttega järjepanu lugeda on vähevõitu – mõtlesin siis, et kasutan oma oskust ja vahendeid ja teen omal pisut lihtsamaks.

Töövahendid, mis mulle appi tulid olid – SQL Server, Excel ja PowerQuery ning PowerShell.

Aga et kõik ilusasti ära rääkida ja üles tunnistada – alustame algusest.

Piibel Veebis

Kõigepealt oli hea meel leida Piibliseltsi tõlkeversioon veebist – http://piibel.net – täielikul ja täiesti loetaval kujul. Tõsi ta on, siin on vaid eestikeelne versioon – on terve hulk veebilehti, kust saaks heebrea-, kreeka-, ladina-, saksa- ja ingliskeelseid tõlkeid. Nende analüüsimise ja koosvaatlemise jätan järgmiseks korraks.

Head sõbrad (kuulsin, et veebiversiooni hoitakse üleval entusiasmist) on veebilehe ehitanud väga hästi ja ülevaatlikult ja mis kõige toredam – lisaks inim-loetavale ka nn masin-loetaval kujul. Lisaks HTML-lehtedele ka XML-formaadis – vaja oli vaid selgeks teha veebiaadresside süsteem ja see oma huvides tööle panna.

Aadresside süsteem on iseenesest lihtne:

http://piibel.net/#q=Ps%208 – päring ’PS 8’ viitab Psalmid peatükk 8

http://piibel.net/#q=Ps%208:7 – päring ’PS 8:7’ viitab sama peatüki 7 värsile

http://piibel.net/.xml?q=Ps%208 – sama peatükk XML-formaadis

http://piibel.net/.xml?q=Ps%208:7 – sama värss XML-formaadis

Nüüd oleks vaid vaja koostada Piibli raamatute (ja lühendite) loetelu ning leida iga raamatu peatükkide arv.

Piibel Excelisse

Raamatute loendiga läks hõlpsasti – see on veebilehel täiesti olemas – ripploend soovitava raamatu leidmiseks. Kuidas seda sealt aga kätte saada?

Kirjeldan lihtsat sammhaaval teekonda:

1. Piiblilehel vajutan F12 – avaneb arendaja keskkond. Ära ehmu!
2. Leiad DOM Exploreri nupu ja seejärel viin kursori ripploendile. Klõps ja alumine sigrimigri (loe HTML kood) keritakse õigesse kohta.
3. Loendile vastav element kokku, paremklõpsates pihku (Copy käsk – CTRL-C ei toiminud) ja sealt (Paste) Excelisse

Nüüd on loend Excelis ja vaja tabeliks vormida. Hea on, kui sul on Excelile (ver 2013) lisatud PowerQuery – otsi ja paigalda, ei maksa midagi aga rõõmu kui palju.

Edasi peab teadma, mida teha

4. PowerQuery ribalt valin From Table – defineerin oma XMLiga pesa tabeliks, ütlen et päist mul ei ole – ta paneb ise.
5. Seejärel otsin päringu editori teisenduste (transform) ribalt käsu PARSE from XML. Tulemuseks tabel ühe veeru ja ühe reaga.
6. Kaks korda laiendan tabelit ja ongi käes raamatute loend. Selle laen Excelisse

Nüüd tuleks tabelit veidi täiendada – tabelis on raamatute nimed koos lühendiga („Esimene Moosese raamat(1Ms)“). Sellest tuleks teha kaks veergu – raamatu nimi ja lühend. Selle lahutamise oleks võinud ju ka PowerQueris teha, sama saaks ka paari valemiga. Seekord olin laisk:

7. Lisasin veeru nimega Raamat, kirjutasin selle esimesele reale „Esimene Moosese raamat“. Teisele reale sain vaevu kirjutada „T“ ja juba Excel pakkus, et teeb selle minu eest lõpuni.
8. Lisasin veel ühe veeru nimega Lühend, kirjutasin selle esimesele reale „1Ms“ ja teisel real piisas kirjutada „2“ – taas Excel teadis, kuidas lõpuni teha.

Viimase sammuna tuli tabelisse saada iga raamatu peatükkide arv – seekord jätan saladuseks, kuidas ma seda tegin, aga valmis ta sain (tegelikult lugesin peatükid käsitsi üle – võtsin ette iga raamatu esimese peatüki ja sellelt liikusin eelmisele, mis oligi eelmise raamatu viimane peatükk).

Piibel andmebaasi

Nüüd tuleks siis piiblisalmid lugeda arvutisse ja sealt andmebaasi nii et edasi võtsin ette SQL Management Studio ja ehitasin omale andmebaasi:

CREATE DATABASE Piibel
go
USE Piibel
go
CREATE TABLE dbo.Raamatud    -- siia tabelisse raamatute loetelu
(
	kood int IDENTITY(1,1) PRIMARY KEY,
	Lühend char(5) NULL,
	Nimetus nvarchar(100) NULL,
	Peatükke int NULL,
)

CREATE TABLE dbo.testxml   -- siia tabelisse Piibli peatükid
(
	kood int IDENTITY(1,1) PRIMARY KEY,
	filedata xml NULL,
)

Ning lõpuks protseduuri, mis kõik veebist loetud (see tuleb veel teha) failid loeb andmebaasi:

USE [Piibel]
go
CREATE proc [dbo].[LaePiibel]
as

declare @x nvarchar(max) ;

with numbrid as

	(select ROW_NUMBER() over (order by r1.kood) n from raamatud r1, raamatud r2)

select @x =
(
	select
	' insert testxml (filedata) SELECT convert(xml, bulkcolumn, 2) FROM OPENROWSET ( BULK ''c:\piibel\' + rtrim(r.Lühend) + '-' + rtrim(cast (n.n as char(3))) + '.xml'', SINGLE_BLOB) as T (bulkcolumn); '

	from raamatud r
	join numbrid n on n.n <= r.Peatükke
	order by r.kood, n
	for xml path('')
);

/* -- seda rida saab kasutada testimiseks (lisa -- rea algusse)
select
-- */ execute
(@x)

Ära küsi kuidas, aga see protseduur toimib. Enne tuleb jahh luua kaust (minul siis c:\piibel) ja lugeda sinna veebist XML failid

PowerShelliga Piiblit lugema

PowerShellis alustasin sellest, et kõigepealt tegin C-kettale kausta c:\Piibel

Seejärel koostasin funktsiooni get-piibel, millele antakse ette kaks parameetrit – raamatu lühend (veebiaadressis vajalik) ja peatükkide arv:

function get-piibel ($raamat, $peatükke )
{

1..$peatükke | ForEach-Object {
	$source = "http://piibel.net/.xml?q=" + $raamat + "%20" + $_.ToString() + "&bv=EST_97&bv=EST_68"
	$destination = "c:\piibel\" + $raamat + "-" + $_.ToString() + ".xml"

	Invoke-WebRequest $source -OutFile $destination

	}
}

Need viimased kaks parameetrit veebiaadressi lõppu tagavad, et alla laetakse kaks erinevat tõlkeversiooni – hiljem hea võrrelda.

Ja nüüd jälle minu laiskus – Exceli tabelis lisandub uus veerg PShell valemiga:

="get-piibel " & [@Lühend] & " " & [@Peatükke]

Veerg kätte (Ctrl-tühik, Ctrl-C) ja PowerShelli aknasse (Ctrl-V) ning käima – piibel hakkab laaduma mu arvutisse ja aeg on pisut hinge tõmmata – võtab pisut aega.

Kuna seesama tabel (raamatunimi, lühend jms) tuleks kanda ka SQL-i, siis samamoodi kui veerg PSchell tekitasin ma veeru sql valemiga:

 
="insert Raamatud (Nimetus, lühend, peatükke) values (N'"&ConcatRange(Table1_2[@[Nimetus]:[Peatükke]];"', N'";TRUE)&"')"

Selles valemis see ConcatRange on mu oma selleks otstarbeks tehtud funktsioon – nii saab hõlpsasti kokku panna suvalise pikkusega insert-lauseid, et siis seal va SQL Manageris kasutada – ikka jälle veerg kätte (Ctrl-tühik, Ctrl-C) ja siis Management Studios lahti (Ctrl-V) ja käima (F5)

ConcatRange funktsioon ise on selline – huviline saab kasutada:

Public Function ConcatRange(r As Range, Delimiter As String, SQL As Boolean) As String

Dim x As Range
Dim s As String
s = ""

For Each x In r.Cells
	s = s & IIf(SQL, Replace(x.Value, "'", "''"), x.Value) & Delimiter
Next

ConcatRange = Left(s, Len(s) - Len(Delimiter))

End Function

Nüüd peaks olema XML failid mul arvutis ja raamatute tabel andmebaasis – saab käivitada varem tehtud protseduuri LaePiibel ning naasta andmebaasi poolele.

Tagasi andmebaasis

Kuna raamatute tabel on nüüd laetud ja piiblifailid samuti – vaatame, mis nendega edasi teha. Tegu on XML kirjetega ja neist tuleks andmed välja lugeda – et see toimiks kiiremini, teeme mõned XML-indeksid. See kiirendab oluliselt XML-kirjete töötlemist ja hilisemad päringukomponendid (XQUERY) teisendatakse lihtsateks relatsioonilisteks komponentideks. XML indeksid ei ole muud kui lahti lammutatud XML hoituna relatsioonilisena.

CREATE PRIMARY XML INDEX xml0 ON dbo.testxml(filedata)
go
CREATE XML INDEX xml1 ON dbo.testxml(filedata) USING XML INDEX [xml0] FOR PATH
CREATE XML INDEX xml2 ON dbo.testxml(filedata) USING XML INDEX [xml0] FOR VALUE
CREATE XML INDEX xml3 ON dbo.testxml(filedata) USING XML INDEX [xml0] FOR PROPERTY

Järgmisena teeme mõned päringud ja salvestame nad view-dena

View ‘värsid’ lubab mul lugeda välja kõik raamatute värsid, neid filtreerida raamatute, tõlkeversiooni ja peatükkide kaupa:

CREATE view [dbo].[värsid]
with schemabinding
as

select

	xx.value('./@id', 'int') nr
	, xx.value('./@heading', 'nvarchar(100)') Pealkiri
	, xx.value('.', 'nvarchar(max)') Värss
	, xx.value('../@id', 'int') Peatükk
	, xx.value('../../@title', 'nvarchar(200)') Raamat
	, xx.value('../../@abbrev', 'nvarchar(200)') Lühend
	, xx.value('../../../@title', 'nvarchar(200)') Piibel
	, right(xx.value('../../../@title', 'nvarchar(200)'),4) PiibliAasta
from dbo.testxml Q
cross apply Q.filedata.nodes('//verse') as T2(xx)

Teine pisut keerukam, lubab mul kahte tõlkeversiooni omavahel kõrvutada:

create view [dbo].[võrdlus]
as

select

	v.Raamat, v.Peatükk, v.nr
	, max(case when piibliaasta = 1968 then v.Värss end) Piibel68
	, max(case when piibliaasta = 1997 then v.Värss end) Piibel97
from värsid v
group by
	v.Raamat, v.Peatükk, v.nr

Nüüd tahaks veel mõned päringud teha, mis aitaks otsida kindlaid nimesid või termineid erinevaist raamatuist. LIKE-operaator on siin pisut paha – esiteks liialt aeglane ja teiseks juba tüütu on erinevaid käändeid ja sõnavorme päringusse kirjutada. Heal meelel kasutaks täistekst indeksit, aga XML tabelile (XML indeksitele) on seda üsna tülikas seadistada (vist ei saagi päris korrektselt). Seepärast koostasin tabeli, kuhu salvestan VIEW ’Värsid’ tulemuse. Kuna täistekst index eeldab tabelil unikaalset indeksit, see peaks olema mõistlikus järjestuses, siis selle tabeli tekitamiseks panin kokku päringu:

select *, identity(integer, 1,1) reanr
into t_värsid
from värsid;
go
create unique clustered index x0 on t_värsid (reanr)

Nüüd saab tabelile lisada juba täistekst indeksid ja teha täistekst predikaatidega päringuid

CREATE FULLTEXT CATALOG [FTCatalog] WITH ACCENT_SENSITIVITY = ON AS DEFAULT;
go
CREATE FULLTEXT INDEX ON dbo.t_värsid(Värss) KEY INDEX x0;

Nüüd saab küsida kõikvõimalke toredaid küsimusi kus CONTAINS-predikaadid, nagu:

select
--top 100
* 
from t_värsid v 
join raamatud r on v.Lühend = r.Lühend

where 1 = 1 -- see tingimus on, et saaks teisi sõltumatult välja kommenteerida

and contains (värss, '"ing*"')
--and contains(värss, '"saatan*"')
--and contains(värss, '"kurat*" or "kurad*"')
--and contains(värss, '"deemon*"')
--and contains(värss, '"jumala lapsed" or "jumala pojad"')
--and contains(värss, 'Jophiel')

and PiibliAasta = 1997

order by r.kood, v.Peatükk, v.nr

Lõpetuseks

Kes nüüd luges ja kaasa mõtles ja proovis, sai ehk endalegi samasuguse andmebaasi, kes aga lihtsalt luges ja tahab proovida, saab omale andmebaasi alla laadida ja paigaldada (RESTORE) aadressilt:

http://bit.ly/PiibelDB

Sinna panen kas mõned SQL scriptid ja Exceli tabeli piibliraamatutega.

Isikukood kuupäevaks – vahelduseks midagi lihtsat

Ikka ja jälle otsitakse “lihtsat” viisi, kuidas isikukoodist välja lugeda kuupäev. Panin siis kirja niihästi, kui mina oskaksin:

=TEXT(((CODE(MID("AABBCC";LEFT(G7;1);1))-47)&MID(G7;2;6));"0000\/00\/00")+0

G7 on seal valemis siis lahter, kus asub isikukood. Testitud ja toimib. Ahjah – vastuselahter (-lahtrid) tuleks panna kuupäeva vormingusse!

Henn

SQL and GEDCOM – VOL 1

Introduction

Two of my hobbies – SQL and genealogy brought me to create SQL database with genealogical data, analyze it, make some statistics and solve graph exercises on it.

Main storage for my genealogical data (and not only for me) is Geni.com and from Geni I can export data to GEDCOM. Some of my friends asked me to make local copy of the info stored in Geni – so this export carries also practical values.

GEDCOM export is a bit limited – 50K person records on time (but in Geni there are close to 100M person records). So, if you want to download a big “family tree”, you need to make several exports. Fortunately same person in different exports is always identified with same id – this allows to merge those exports later.

Today I can play with quite big (in local mean) databases – approx 2M persons. This is a result of close to 200 exports. I can test both – performance things and graph algorithms.

During this I have solved several SQL-oriented problems and built one nice Excel-tool for searching different paths between persons. I can’t share data but I’m happy to share tools I’ve created. So, anyone interested in can try and play with their own exports.

Current article is 1st from a series – preparation. In next article I plan to describe how to extract info from prepared data. The third will demonstrate some path-finders and after that I plan to play a bit with Excel and genealogical data. The last one will try to generate a new GEDCOM with different scope from combined GEDCOM-s – targeted to for other software like FamilyTreeMaker or similar.
 

0. GEDCOM structure (short review)

GEDCOM is a typical and standardized way to store and exchange genealogical (and not only) information between different applications.

0 @I1448552@ INDI
1 NAME Henn /Sarv/
2 GIVN Henn
2 SURN Sarv
2 NICK सींग
1 SEX M
1 BAPL
2 DATE 20 APR 1929
1 ENDL
2 DATE 21 NOV 1929
1 BIRT
2 DATE 7 MAR 1955
2 ADDR
3 CITY Tallinn
3 CTRY Estonia
1 GRAD
2 DATE 1961
…
1 FAMC @F6000000001528333119@
1 FAMS @F6000000001656449693@
2 NOTE {geni:marriage_order} 1
1 FAMS @F6000000001646452992@
2 NOTE {geni:marriage_order} 2

GEDCOM file is a text file containing rows with objects, attributes, sub-attributes and references between them. Objects (INDI – person, FAM – family jne), object attributes (NAME, BIRT), components of attributes (components of NAME: GIVN, SURN; components of BIRT: DATE and ADDR), components of components (components of ADDR: CITY, CTRY) etc. Each row begins with number indicating the level (0 – object, 1 – attribute, 2 – sub-attribute etc). That means – bigger level belongs to lower – hierarchical structure.

The sample GEDCOM fragment that was brought above tells that PERSON with ID=“I1448552” has NAME=“Henn Sarv” (first name GIVN “Henn”, family name SURN “Sarv”, nickname NICK “सींग”) and he was born on (BIRT) date (DATE “7 MAR 1955”), in place (ADDR CITY “Tallinn” CTRY “Estonia”).

Object might refer to other objects. Henn’s “object set” has reference to 3 families (FAM) – one where Henn belongs as a child (FAMC with ID=“F6000000001528333119”) and two where Henn belongs as a parent (FAMS).

Families are later in GEDCOM as objects (row-sets) like:

0 @F6000000001528333119@ FAM
1 MARR
2 DATE 28 AUG 1936
1 HUSB @I6000000001528333113@
1 WIFE @I6000000001528640081@
1 CHIL @I6000000001592998311@
1 CHIL @I6000000001604210254@
1 CHIL @I6000000001604632474@
1 CHIL @I6000000001528309143@
1 CHIL @I6000000001617582391@
1 CHIL @I6000000012433193658@
1 CHIL @I1448552@

This example describes a family with ID=“F6000000001528333119”. The parents were married (MARR) on date (DATE “28 AUG 1936”), the place of the marriage is missing (not known). The father of the family (HUSB) is the person with ID=“I6000000001528333113”, mother (WIFE) is the person with ID=“I6000000001528640081” and there are 7 kids in the family (CHIL).

1. First phase – reading GEDCOM rows into database

• problems – UTF-8 / Unicode
• line numbering

BULK INSERT command is suitable to read a GEDCOM file into database, but there we have 3 problems:

1st – filename parameter can’t be a variable nor an expression – so I have to use built SQL (exec (@x));

2nd – SQL server doesn’t accept (interpret correctly) UTF-8, the format I get from Geni export, so I have to convert from UTF-8 to UNICODE. For that I can use POWERSHELL:

Get-Content -Encoding UTF8 -path $x | Set-Content -Encoding Unicode –path $x

3rd is numbering of the lines. In relational databases all sets (tables) are un-ordered, even when table have clustered index because reading the table might happen in parallel. So, during the load I have to assign unique and rising number to each row – order of rows is extremely important in GEDCOM. BULK INSERT allows to load only info I get from file, so INSERT FROM OPENROWSET doesn’t work here.

I solved this in the following way:

• created table with IDENTITY column for storing table rows
• created view containg only line-column from that table
• BULK INSERT target will be this view

Result is that rows from the file will be stored in the table according to their ordinal numbers and they can be uniquely referenced using this number.

For logging and history I created one more table to store loading history.

1.1 Creating necessary objects

• Table gedcom_data::

CREATE TABLE dbo.gedcom_data
(
	nr bigint IDENTITY(1,1) PRIMARY KEY,
	line nvarchar(1024) NULL,
)

• View gedcom_file:

CREATE VIEW dbo.gedcom_file
AS
SELECT line FROM dbo.gedcom_data

• Table load_history:

CREATE TABLE dbo.load_history
(
jrknr int IDENTITY(1,1) PRIMARY KEY,  -- jrknr means [loading] order number
first_nr bigint NOT NULL,
ridu bigint NOT NULL,  -- ridu means count of lines
filename nvarchar(258) NULL,
load_date datetime NULL
)

• Procedure Loe_Gedcom:

CREATE PROCEDURE [dbo].[Loe_Gedcom] (@file nvarchar(256))
as
select h.load_date, h.filename from load_history h where h.filename = @file
IF @@ROWCOUNT = 0
BEGIN
	declare @x nvarchar(max) = 
'
DECLARE @i int = (select coalesce(max(nr), 0) from dbo.gedcom_data) + 1
IF @i = 1 set @i = (select coalesce(max(nr), 0) from dbo.gedcom_data_arhiiv) + 1
begin tran;
bulk insert dbo.gedcom_file
from ''' + @file + '''
with (
rowterminator = ''\r''
, datafiletype = ''widechar''
);
insert dbo.load_history (first_nr, ridu, filename) values (@i, @@rowcount, ''' + @file + ''');
commit;
'
execute(@x)
end

else
print 'allready loaded: ' + @file

1.2 Execution:

exec Loe_Gedcom 'c:\kataloog\failinimi.ged'

Once per each file exported from Geni. Sometimes I create those exec-s manually, sometimes using POWERSHELL.

1.3 Correction:

update gedcom_data set line = ltrim(replace(line, char(10), ''))

2 Initial extraction

Next I need to make some row-level modifications. In case of 1-2 GEDCOM files this isn’t a challenge but when I load together several tens or hundreds of files I need to implement batch-wise processing to keep transaction log and transaction duration under control.

Exported (and now loaded) GEDCOM files contain a lot of meaningless (for my goal) rows (approx 50%), it’s reasonable to filter out the those rows without deleting them, because later I might still need to use them to create a summary GEDCOM file from the merged ones.

For this reason I’ve created a new table containing only required rows and with new columns with parsed dates and id-s.

At the beginning I created a view to show me only the rows I need and then I created a table to store those rows. With small number of rows this technique worked but when I needed to implement batch-wise process (because of the large number of rows) I needed to convert this view to procedure.

2.1 Create objects:

• View parsed_view:

CREATE VIEW [dbo].[parsed_view]
as
with t1 as
(
	select
	nr
	, TRY_PARSE(left(line,2) as int) lvl
	, line
	, CHARINDEX('@', line) [start] -- 1st occurence of @
	from dbo.gedcom_data
),
t2 as
(
	select *
	, case when [start] > 0 then CHARINDEX('@', line, [start]+1) end [stop]
	from t1
	where lvl is not null
),
t3 as
(
	select *
	, case when [start] > 0 and [stop] > 0 then SUBSTRING(line, [start], [stop]-[start]+1) end kood
	, case when [start] = 3 then SUBSTRING(line, [stop] + 2, 4) else SUBSTRING(line, 3, 4) end class
	, case when [start] <> 3 then ltrim(substring(line, 7, 100)) end data
	from t2
)
select nr, lvl,
case when class in ('indi', 'fam') then kood end kood,   -- important to read codes only from right rows
class, data
, case when class = 'DATE' then try_parse (data as date using 'en-us') end as datevalue
, case when class = 'DATE' then data end as datetext
, case when class = 'NAME' then REPLACE(data, '/', '') end as name
from t3
where class not in -- unneccessary 'classes'
(
'head','titl', 'sour', 'vers', 'subm',
'gedc', 'vers', 'form', 'char', 'nick',
'cont', 'note', 'addr', 'rfn', 'time',
'obje', 'occu', 'phon', 'plac', 'post',
'chan', 'form', 'file', 'city'
)

This view was created step-by-step using CTE (common table expression). At first I needed to find code in rows between „@“ and „@“.

However, MS SQL implementation (T-SQL) doesn’t allow to reuse projected aliases in projection, so I needed to create 3 expressions: t1 find first occurence of „@“ (if exists), t2 find second occurence of „@“(if exists) and t3 extract the required code with SUBSTR.

Level numbering at the beginning of the row goes into [lvl], class name (type of row) into [class]. NB! Expression t3 from t2 needs to differentiate – in rows of level 0, the class comes from the end of the row, but in the other rows it comes from the beginning.

And finally 2 simple conversions:

1. with class NAME, filling column [name] (with rempved ‘/’)
2. with class DATE, trying fill [datevalue] and filling [datetext]
3. and last – excluding rowsd (classes) unused in system

• Table parsed_data:

CREATE TABLE dbo.parsed_data(
nr bigint constraint parsed_data_x0 primary key,
lvl int NULL,
kood char(24) NULL,
class char(4) NULL,
data nvarchar(1000) NULL,
datevalue date NULL,
datetext nvarchar(40) NULL,
name nvarchar(128) NULL,
)

CREATE NONCLUSTERED INDEX x1 ON dbo.parsed_data (kood)
INCLUDE (class, data, datevalue, datetext, name)

CREATE NONCLUSTERED INDEX x2 ON dbo.parsed_data (class)
INCLUDE (nr, datevalue, datetext)

CREATE NONCLUSTERED INDEX x3 ON dbo.parsed_data (lvl, kood)
INCLUDE (nr)

CREATE NONCLUSTERED INDEX x4 ON dbo.parsed_data (class)
INCLUDE (nr, kood, data)

This table is to store (materialize) previous view results. Indexes are added during the work by execution plan recommendations (missing index detail).

• Protseduur parsed_load

Initially I used INSERT-SELECT to store data but batch-wise processing requires some modifications. I can find row for starting next batch but where on view level wasn’t visible for query optimizer on t1-expression level so where nr > @start was applyed on last expression level. To increase performance I created procedure doing exactly same as INSERT and SELECT.

Initial version of the batch-processing:

declare @start bigint =
(select top 1 nr from parsed_data order by nr desc)
-- most fast way to get 1st number to process

declare @batch int = 1000000

insert top (@batch) parsed_data
select * from parsed_view
where nr > @start

The procedure converted from the initial view:

CREATE PROCEDURE dbo.Parsed_Load
@batch int = 1000000
as
declare @start bigint = (select coalesce( (select top 1 nr from parsed_data order by nr desc), 0))
;
with t1 as
(
	select nr
	, TRY_PARSE(left(line,2) as int) lvl
	, line
	, CHARINDEX('@', line) algus
	from dbo.gedcom_data
	where nr > @start
	-- main difference - where is transfered to expression t1
),
t2 as
(
	select *
	, case when algus > 0 then CHARINDEX('@', line, algus+1) end lõpp
	from t1
	where lvl is not null
),
t3 as
(
	select *
	, case when algus > 0 and lõpp > 0 then SUBSTRING(line, algus, lõpp-algus+1) end kood
	, case when algus = 3 then SUBSTRING(line, lõpp + 2, 4) else SUBSTRING(line, 3, 4) end class
	, case when algus <> 3 then ltrim(substring(line, 7, 100)) end data
	from t2
),
t4 as
(
	select nr, lvl,
	case when class in ('indi', 'fam') then kood end kood,   
	-- oluline, et koodid tulevad vaid õigetest ridadest
	class, data
	, case when class = 'DATE' then try_parse (data as date using 'en-us') end as datevalue
	, case when class = 'DATE' then data end as datetext
	, case when class = 'NAME' then REPLACE(data, '/', '') end as name
	from t3
	where class not in
	(
	'head','titl', 'sour', 'vers', 'subm',
	'gedc', 'vers', 'form', 'char', 'nick',
	'cont', 'note', 'addr', 'rfn', 'time',
	'obje', 'occu', 'phon', 'plac', 'post',
	'chan', 'form', 'file', 'city'
	)
)
insert top (@batch) parsed_data
select * from t4

2.2 Execution batch-wise

The last procedure could be executed in the following loop:

select 1 -- to guarantee that @@rowcount > 0
while @@ROWCOUNT > 0
begin
	checkpoint -- to minimize transaction log
	exec Parsed_Load 1000000 --  batchsize
end

Other batch-wise loops I execute in the same way.

The origin rownumbers (column [nr]) are good for referencing last executed row, start a new batch and later reference to “source”.

2.3 Once loaded then archive

• Gedcom_Data_arhiiv
• batch-wise delete
• Truncate table delete

Once the rows are loaded, it is good to archive them. By intent I didn’t use partitioned table – probably better way than I used.

The most simple way to archive loaded rows (at beginning I used this way):

set xact_abort on
begin tran

insert dbo.gedcom_data_arhiiv select * from dbo.gedcom_data
delete dbo.gedcom_data
commit

Again, when I load exported GEDCOM files one at a time, this solution works. I tried to reload all my 200 GEDCOM files and the solution failed – transaction log got full.

I made a loop:

declare @batch int
declare @start bigint
select @batch = 1000000
while @@rowcount > 0
begin

checkpoint
select @start = coalesce((select top 1 nr from gedcom_data_arhiiv order by nr desc), 0)

insert top (@batch) gedcom_data_arhiiv
select * from gedcom_data where nr > @start

end

After that I need to delete the archived rows. Deleting rows (by one or batch-wise) isn’t the shortest way, but it’s dangerous to truncate, because I need to keep identity value and truncate destroys this.

The solution is to simply save the last identity and restore after truncate:

declare @nr bigint
= (select top 1 nr from gedcom_data order by nr desc)

truncate table gedcom_data

dbcc checkident (gedcom_data, reseed, @nr)

3. Transfer data between rows

• Date transfer to event rows
• Code transfer between master (object) and detail (rest) rows

The following step makes the rows “independent”. Current rows depend from the info in other rows. Some (simplest and most important) techniques could be combined to simplify following queries.

1. 1st is transferring date info. Each DATE-class row (level 3) is immediately followed by event rows (BIRT, DEATH, MARR etc – level 2). It is reasonable to transfer DATE info into matching event row.
2. 2nd is transferring codes. Person and family rows (level 0) contain the codes of person and family. It would be best for the following queries to have person and family codes in each row they belong to. All rows (level >0) belong to the last (level 0) object row.

The slowest way is to use cursors. It’s tested. One GEDCOM file under cursor might run 15min or longer. With 200 GEDCOM files we get an unacceptable time slice.

3.1 Procedure Date_update 2 versions

The initial procedure simply self-joined the table using neighbor rows. Still, rising number of rows decreases performance too much.

CREATE PROCEDURE [dbo].[Date_Update]
as
update d
set datevalue = coalesce(d.datevalue,e.datevalue), datetext = coalesce(d.datetext,e.datetext)
--select *
from dbo.parsed_data d
join dbo.parsed_data e on d.nr+1 = e.nr
where d.class in ('birt', 'deat', 'marr', 'div') and e.class = 'date'

I guessed that I can prevent sort because table (and self) are joined by same [nr], but during testing I didn’t get the best execution plan.

I had to create another version – the new one works so fine that there is no need for batch-wise:

CREATE PROCEDURE [dbo].[Date_Update_2]
as
with e as
(
	select nr, datevalue, datetext
	, lead(datevalue) over (order by nr) n_datevalue
	, lead(datetext) over (order by nr) n_datetext
	, lead(class) over (order by nr) n_class
	from parsed_data d
)
update parsed_data

set datevalue = coalesce(d.datevalue,n_datevalue), datetext = coalesce(d.datetext,n_datetext)

from parsed_data d
join e on e.nr = d.nr
where n_class = 'date'
and d.class in ('birt', 'deat', 'marr', 'div')

3.2 Procedure Kood-Flow – flowing codes from master rows to others

CREATE PROCEDURE [dbo].[Kood_Flow]  
@batch int = 1000000
as
with koodid as
(
	select nr, kood
	from dbo.parsed_data where lvl = 0 and kood is not null
)
, vahemik as
(
	select
	nr, kood,
	(lead(nr) over (order by nr))-1 nnr
	from koodid
)
update top (@batch) v
set kood = i.kood
from dbo.parsed_data v
join vahemik i on v.nr between i.nr and i.nnr
where v.kood is null

This procedure virtually makes 2 tables – one contains of the master rows (level 0) containing codes to transfer and the other contains of the detail rows (level >0) belonging to master rows and waiting for the codes.

Once master table is formed (expression ‘koodid’) using lead we can find row of the next master row and all details with [nr] between those 2 masters get code from the previous master.

As we update only code-less rows the procedure can be executed batch-wise.

select 1
while @@ROWCOUNT > 0
exec Kood_Flow 1000000

Next time …

… I will explain how to easily extract person, family and relations.