Du har säkert märkt att alla pratar om artificiell intelligens och maskininlärning i dag. Med all rätt, för det är ett mycket viktigt ämne som kommer att forma vår framtid. När vi tittar på de flesta koder som rör maskininlärning och som finns på nätet slår det oss dock hur mycket ”kodning” folk faktiskt använder för att förbereda data. I många fall är indata ”bara en matris” och man har lagt ner mycket tid (och ibland minne) på att förbereda den.

Den fråga som naturligtvis uppstår i vårt fall är: Varför inte förbereda data i SQL? Det är enklare, mindre arbete och mycket mer flexibelt.

Skapa några exempeldata

För att visa vad vi kan göra på PostgreSQL-sidan skapar vi en enkel tabell med bara 20 slumpmässiga värden. Låt oss först definiera en tabell:

test=# CREATE TABLE inputdata
(
	id 		int,
	data 	numeric 	DEFAULT random() * 1000
);
CREATE TABLE

The table is populated:

test=# INSERT INTO inputdata
	SELECT * FROM generate_series(1, 20);
INSERT 0 20

Vi har nu en tabell med 20 slumpmässiga värden. I verkligheten kommer du redan att ha några befintliga data som du kommer att arbeta med:

test=# SELECT * FROM inputdata;
 id |       data       
----+------------------
  1 |  542.76927607134
  2 | 813.954454381019
  3 |  215.18046176061
  4 | 989.989245776087
  5 | 142.890753224492
  6 | 326.086463406682
  7 | 24.8975520953536
  8 | 266.512574627995
  9 | 86.0621216706932
 10 | 801.756543107331
 11 | 790.149183012545
 12 | 317.997705657035
 13 | 975.230060052127
 14 | 385.490739252418
 15 | 746.592517476529
 16 | 621.084009762853
 17 | 208.689162041992
 18 | 529.119417071342
 19 | 260.399237740785
 20 | 563.285110052675
(20 rows)

Tankar om provtagning, utbildning och kontroll

Om du tränar en AI-modell (kanske en Support Vector Machine / SVM, ett neuralt nätverk eller något annat) börjar du alltid med att dela upp data i olika delar:

  • Träningsdata för din AI-modell
  • Testdata för din AI-modell.

Träningsdata används för att lära upp din modell. Testdata används sedan för att kontrollera om din modell fungerar bra. Att dela upp data är viktigt och i viss mån nyckeln till framgång.

Provtagning av tabeller i PostgreSQL

Sedan version 9.5 har vi redan provtagning i PostgreSQLs kärna. Här är ett exempel:

test=# SELECT *
	FROM inputdata TABLESAMPLE BERNOULLI (50)
				REPEATABLE (87);
 id |       data       
----+------------------
  3 |  215.18046176061
  4 | 989.989245776087
  5 | 142.890753224492
 10 | 801.756543107331
 11 | 790.149183012545
 12 | 317.997705657035
 14 | 385.490739252418
 15 | 746.592517476529
 16 | 621.084009762853
 19 | 260.399237740785
 20 | 563.285110052675
(11 rows)

Du måste lägga till TABLESAMPLE-klausulen i tabellen och ange hur det ska fungera. I det här exemplet har jag beslutat att använda Bernoulli-tabellens provtagningsmetod (TSM) och sagt att den ska vara upprepningsbar. När det gäller maskininlärning är det verkligen vettigt att använda klausulen REPEATABLE eftersom vi vill försäkra oss om att vi kan träna vår modell om och om igen med samma indata. Vi kan köra koden hur ofta vi vill och PostgreSQL kommer alltid att returnera samma prov (förutsatt naturligtvis att de underliggande uppgifterna inte ändras).

Maskininlärning: Massor av data …

Hittills låter allting bra. Det finns dock en liten nackdel med detta tillvägagångssätt. En toppmodern modell behöver en MYCKET stor mängd indata för att fungera bra. Vi talar om många miljoner rader. Vid någon tidpunkt kommer vi att ställas inför en fråga som visas nedan (skriven i pseudokod):

SELECT	*
FROM 	inputdata
WHERE	id NOT IN (SELECT id FROM test_data_set);

Det finns två problem här:

  • Vi måste lagra testdata någonstans, vilket kräver mycket utrymme.
  • Den stora NOT IN-angivelsen är ganska dyr.

Så det kanske finns något annat sätt att göra det på? Jag kom fram till en ganska enkel metod som gör jobbet.

Förbereda data för maskininlärning i PostgreSQL

Eftersom det inte finns någon ”NOT IN TABLESAMPLE”-klausul i PostgreSQL och eftersom vi inte vill duplicera våra data är tanken att använda en vy som kan användas för att extrahera urvalet:

test=# CREATE VIEW preparation_step1 AS
	SELECT 	*, abs(hashtext(id::text) % 100)
	FROM 	inputdata ;
CREATE VIEW

Den första idén är att använda hashtext-funktionen för att omvandla indata till en hash. Hashtext returnerar jämnt fördelade tal, vilket är precis vad vi vill ha här. Genom att lägga till ”modulo 100” skapas 100 dataskivor (1 % vardera). Var medveten om att hashtext-funktionen kan returnera negativa värden. Funktionen ”abs” kommer att omvandla värdet till positiva värden.

test=# \x
Expanded display is on.
test=# \df *hashtext*
List of functions
-[ RECORD 1 ]-------+-----------
Schema              | pg_catalog
Name                | hashtext
Result data type    | integer
Argument data types | text
Type                | normal

När du tittar på vyn är resultatet redan ganska användbart:

test=# SELECT * FROM preparation_step1 LIMIT 10;
 id |       data       | abs
----+------------------+-----
  1 |  542.76927607134 |  47
  2 | 813.954454381019 |  26
  3 |  215.18046176061 |   4
  4 | 989.989245776087 |  92
  5 | 142.890753224492 |  58
  6 | 326.086463406682 |  12
  7 | 24.8975520953536 |  95
  8 | 266.512574627995 |  88
  9 | 86.0621216706932 |  36
 10 | 801.756543107331 |  81
(10 rows)

Nu kan vi filtrera bort data. Ett exempel: Exempel: ”abs < 50” kan vara träningsdata och resten kan användas för att validera och kontrollera våra modeller.

Det här tillvägagångssättet är i princip bra om ditt dataset är riktigt stort (xxx miljoner rader eller så). Det kanske inte är optimalt om ditt dataset är för litet. I det här fallet är det bättre att använda samplingmekanismer som tillhandahålls av ditt favoritbibliotek (TensorFlow, sklearn osv.). Varför är det så? Om du använder en liten datamängd (t.ex. endast ett par hundra rader) löper du risken att skapa ett snedvridet urval. Vad innebär det? Anta att du har ett dataset som innehåller information om män och kvinnor. Du vill att urvalet ska visa samma fördelning som den ursprungliga datamängden – alltså samma procentandel män och kvinnor. Lösningen på problemet kallas ”stratifiering” och stöds av bibliotek som sklearn och andra. I mitt enkla SQL-exempel utgår jag från att mängden data som matas in till modellen är riktigt stor och att stratifiering därför inte är något problem.