Składnia:
CREATE | REPLACE TABLE table-name AS select-statement TRANSFORM: Gython-code-line [Gython-code-line]* [GLOBALS: Global-Gython-code-line [Global-Gython-code-line]* ] [KEEP | DROP column-name-1[, column-name 2[, ...]]
W GDBase możliwe jest łączenie instrukcji SQL z poleceniami języka Gython w celu przetwarzania danych w poleceniu CREATE TABLE ... TRANSFORM. Funkcjonalność tego polecenia jest podobna do działania procedury trans.
Kod Gythona jest umieszczany po słowach kluczowych TRANSFORM i GLOBALS i musi być wcięty w stosunku do tych słów. Kod po TRANSFORM jest wykonywany raz dla każdego wiersza tabeli. Kod po GLOBALS jest wykonywany raz przed rozpoczęciem przetwarzania wierszy w tabeli.
Dodatkowo, po słowie kluczowym KEEP można podać za pomocą nazw lub wyrażeń regularnych kolumny, które mają pozostać w tabeli wynikowej. Alternatywnie, po słowie kluczowym DROP można podać kolumny, które mają zostać usunięte z wynikowej tabeli.
Przykład 33.1. Podstawowa składnia
# input table:
table 'TAB1':
format c DATE 'yyyy-MM-dd'
a b c d
'aaa' 10 '2008-12-04' 10
'bbb' 40 '2007-11-06' 20
'ccc' -20 '2000-09-11' 45
sql:
REPLACE TABLE TAB2 AS
SELECT *, a || b AS con FROM TAB1
TRANSFORM: # the python part; pay attention to indents !!!
e = b * 10 # standard python operation
f = f1(b,d) # execution of a user-defined function
sum = sum + b # agregation
g = sum # writing the value of global variable to the output column
h = sin(b) # execution of a function from the imported package 'math'
i = c.weekday()
j = c.year
GLOBALS:
sum = 0 # initialization of a global variable
from math import * # importing packages
from datetime import *
def f1(arg1,arg2): # defining a user function
if arg1 == arg2:
f1result = 1
else:
f1result = 0
return f1result
DROP a, b, c # dropping some of the columns
trans None <- 'TAB2':
print sum, d, e ,f ,g ,h, i, j, con, sum
Wynik:
10 10 100 1 10 -0.5440211108893698 3 2008 aaa10 10 50 20 400 0 50 0.7451131604793488 1 2007 bbb40 50 30 45 -200 0 30 -0.9129452507276277 0 2000 ccc-20 30
W części GLOBALS można zaimportować następujące moduły/pakiety języka Gython:
| __builtin__ |
| __main__ |
| _ast |
| _codecs |
| _md5 |
| _random |
| _sha |
| _sre |
| _struct |
| _symtable |
| _testcapi |
| _types |
| _weakref |
| array |
| cStringIO |
| cmath |
| collections |
| datetime |
| errno |
| exceptions |
| gc |
| imp |
| itertools |
| marshal |
| math |
| operator |
| posix |
| pwd |
| signal |
| strop |
| sys |
| thread |
| time |
| unicodedata |
| zipimport |
Kolumny w tabeli utworzonej przez polecenie SELECT są dostępne w kodzie po TRANSFORM jako zmienne o takich samych nazwach. Wszelkie inne zmienne utworzone w części TRANSFORM oraz GLOBALS również pojawią się jako kolumny tworzonej tabeli. Aby określić kolumny, które mają się składać na ostateczną tabelę wynikową należy użyć klauzuli KEEP lub DROP.
Za pomocą słownika __vars__ można uzyskać dostęp do wszystkich zmiennych, które są zdefiniowane w danym momencie wykonywania kodu. Na przykład __vars__['x'] odnosi się do wartości zmiennej x, a instrukcja __vars__['y'] = 15 przypisze zmiennej y wartość 15; jeżeli zmienna o nazwie y w danym momencie nie istnieje, to zostanie ona przedtem utworzona.
Przykład 33.2. Zastosowanie __vars__
Głównym zastosowaniem funkcji __vars__ jest tworzenie lub dostęp do zmiennych z nazwami stworzonymi w kodzie Gython.
for i in range(3)
__vars__['x_%d'%1] = 1
Ten kod stworzy zmienne x_1, x_2, x_3 odpowiednio z wartościami 1, 2, 3.
Dodatkowo, przykład Transpozycja tabeli pokazuje jak wykorzystać zmienną __vars__ do wykonania transpozycji tabeli.
Jeżeli w trakcie przetwarzania tabeli zachodzi potrzeba pominięcia pewnych wierszy w tabeli wynikowej, to można skorzystać ze zmiennej __skipRow__. Przypisanie tej zmiennej wartości 1 sprawi, że dany wiersz nie zostanie włączony do tabeli wynikowej, podczas gdy przypisanie __skipRow__ = 0 będzie miało przeciwny skutek.
Funkcjonalność zmiennej __skipRow__ zilustrowana jest w przykładach Wybieranie ostatniego id w dniu, Transponowanie za pomocą grup i Transponowanie za pomocą __save__.
W instrukcjach CREATE TABLE ... TRANSFORM możliwe jest grupowanie przetwarzanych wierszy w połączeniu z klauzulą ORDER BY.
Przykład 33.3. Przetwarzanie w grupach - część 1
sql:
REPLACE TABLE TAB2 AS
SELECT purpose FROM german_credit
ORDER BY purpose
TRANSFORM:
gr_begin = __group__.start(0) # indicator of group beginning
gr_end = __group__.end(0) # indicator of group end
if gr_begin == 1:
tmp = 1
else:
tmp = tmp + 1
if gr_end == 1:
how_many_1 = tmp
GLOBALS:
tmp = 0
DROP tmp
sql:
REPLACE TABLE TAB3 AS
SELECT * FROM TAB2
ORDER BY purpose, how_many_1 desc
TRANSFORM:
if gr_end == 1:
tmp = how_many_1
how_many_2 = tmp
GLOBALS:
tmp = 0
DROP how_many_1, tmp
sql TAB3_out:
SELECT * FROM 'TAB3'
ORDER BY gr_begin DESC, purpose ASC
LIMIT 10
print TAB3_out
Wynik:
purpose | gr_begin | gr_end | how_many_2 | +-------------------+----------+--------+------------+-- business | 1 | 0 | 97 | domestic appliance | 1 | 0 | 12 | education | 1 | 0 | 50 | furniture/equipment | 1 | 0 | 181 | new car | 1 | 0 | 234 | other | 1 | 0 | 12 | radio/tv | 1 | 0 | 280 | repairs | 1 | 0 | 22 | retraining | 1 | 0 | 9 | used car | 1 | 0 | 103 |
Jeżeli użyta jest klauzula ORDER BY wiersze są dzielone na grupy według wartości zmiennych na podstawie których są sortowane. Wówczas w bloku TRANSFORM dostępny jest obiekt __group__, który posiada dwie metody:
Argument group_no określa numer grupy. Wartość 0 odpowiada najbardziej wewnętrznej grupie, wynikającej z sortowania po pierwszej zmiennej w klauzuli ORDER BY, itd. Pominięcie tego argumentu oznacza, że metoda zwróci ostatnią, najbardziej szczegółową grupę.
Przykład 33.4. Przetwarzanie w grupach, część 2.
table 'TAB4':
a b c
1 1 1
1 1 2
1 2 3
2 1 1
2 1 2
2 2 3
sql:
REPLACE TABLE TAB5 AS
SELECT * FROM TAB4
ORDER BY a, b, c
TRANSFORM:
s2 = __group__.start(2)
e2 = __group__.end(2)
s1 = __group__.start(1)
e1 = __group__.end(1)
s0 = __group__.start(0)
e0 = __group__.end(0)
sql TAB5_out:
SELECT * FROM TAB5
print TAB5_out
Wynik:
a | b | c | s2 | e2 | s1 | e1 | s0 | e0 | +-+---+---+----+----+----+----+----+----+-- 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 | 1 | 2 | 1 | 1 | 0 | 1 | 0 | 0 | 1 | 2 | 3 | 1 | 1 | 1 | 1 | 0 | 1 | 2 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 0 | 2 | 1 | 2 | 1 | 1 | 0 | 1 | 0 | 0 | 2 | 2 | 3 | 1 | 1 | 1 | 1 | 0 | 1 |
Poniższy przykład pokazuje, jak łatwo znaleźć 'najnowsze' informacje w zbiorze danych.
Przykład 33.5. Wybieranie ostatniego id dla danego dnia
table 'IDS':
format day DATE 'yyyy-MM-dd'
day id
'2005-03-21' 124345324
'2005-03-22' 3252352
'2005-03-23' 325235
'2005-03-23' 43252435
'2005-03-23' 33333333
'2005-03-23' 1111111111
'2005-03-24' 43333333
'2005-03-24' 34355555
'2005-03-24' 99999999
sql:
REPLACE TABLE IDS_last AS SELECT * FROM IDS
ORDER BY day, id
TRANSFORM:
if __group__.end(0):
__skipRow__ = 0
else:
__skipRow__ = 1
sql IDS_last_out:
SELECT * FROM IDS_last
print IDS_last_out
Output:
day | id | +----------+------------+-- 2005-03-21 | 124345324 | 2005-03-22 | 3252352 | 2005-03-23 | 1111111111 | 2005-03-24 | 99999999 |
Za pomocą przetwarzania w grupach można również wykonać transpozycję danych w tabeli.
Przykład 33.6. Transpozycja, część 1
table 'clients':
client statistics value
'john' 'amount' 100000.0
'john' 'duration' 17.0
'john' 'packages' 3
'eve' 'amount' 5000.0
'eve' 'duration' 5.0
'eve' 'packages' 1
'peter' 'amount' 0.0
'peter' 'duration' 36.0
'peter' 'packages' 12
sql:
REPLACE TABLE clients2 AS
SELECT *
FROM clients
ORDER BY client
TRANSFORM:
if __group__.start():
names = []
values = []
names.append(statistics)
values.append( value )
if __group__.end():
for i in range(len(names)):
__vars__[names[i]] = values[i]
else:
__skipRow__ = 1
DROP statistics, i
sql s:
SELECT * FROM clients2
print s
Output:
client | value | amount | duration | packages | +------+-------+----------+----------+----------+-- eve | 1.0 | 5000.0 | 5.0 | 1.0 | john | 3.0 | 100000.0 | 17.0 | 3.0 | peter | 12.0 | 0.0 | 36.0 | 12.0 |
Funkcja __save__(['save-table-name']) służy do natychmiastowego zapisania wiersza w tabeli save-table-name. Jeżeli nazwa tabeli nie zostanie podana, wiersz jest zapisywany do tabeli utworzonej przez polecenie CREATE/REPLACE TABLE.
Po zapisaniu wiersza kod Gythona jest nadal wykonywany.
Jeżeli tabela o nazwie save-table-name nie istnieje, zostanie ona utworzona. Jeżeli istnieje, to zostanie ona nadpisana.
Przykład 33.7. Funkcja __save__()
table 'TAB1':
format c DATE 'yyyy-MM-dd'
a b c d
'aaa' 10 '2008-12-04' 10
'bbb' 40 '2007-11-06' 20
'ccc' -20 '2000-09-11' 45
sql:
REPLACE TABLE TAB6 AS
SELECT * FROM TAB1
TRANSFORM:
if a == 'aaa':
__save__()
a = a + 'bye'
sql a:
SELECT * FROM TAB6
print a
Output:
a | b | c | d | +------+-----+------------+----+-- aaa | 10 | 2008-12-04 | 10 | aaabye | 10 | 2008-12-04 | 10 | bbb | 40 | 2007-11-06 | 20 | ccc | -20 | 2000-09-11 | 45 |
Dzięki funkcji __save__ można wykonać transpozycję tabeli w inny sposób.
Przykład 33.8. Transpozycja, część 2
table 'clients2':
client amount duration packages
eve 5000.0 5.0 1.0
john 100000.0 17.0 3.0
peter 0.0 36.0 12.0
sql:
REPLACE TABLE clients AS SELECT * FROM clients2 TRANSFORM:
value = amount
statistics = 'amount'
__save__()
value = duration
statistics = 'duration'
__save__()
value = packages
statistics = 'packages'
__save__()
__skipRow__=1
KEEP client, value, statistics
sql s:
SELECT * FROM clients
print s
# the same code may be realized a little bit differently:
# sql:
# REPLACE TABLE clients AS SELECT * FROM clients2 TRANSFORM:
#
# for statistics in ['amount', 'duration', 'packages']:
# value = __vars__[statistics]
# __save__()
#
# __skipRow__=1
#
# KEEP client, value, statistics
Output:
client | value | statistics | +------+----------+------------+-- eve | 5000.0 | amount | eve | 5.0 | duration | eve | 1.0 | packages | john | 100000.0 | amount | john | 17.0 | duration | john | 3.0 | packages | peter | 0.0 | amount | peter | 36.0 | duration | peter | 12.0 | packages |
Poniższy przykład pokazuje, jak w bloku TRANSFORM można się odwoływać do wcześniejszych wierszy.
Przykład 33.9. Uzyskiwanie wartości z wcześniejszych wierszy
table 'chf':
format When DATE 'yyyy-MM-dd'
When Exchange Diff
'2009-05-04' 2.9068 0.08
'2009-05-05' 2.8753 -1.08
'2009-05-06' 2.9134 1.33
'2009-05-07' 2.8649 -1.66
'2009-05-08' 2.8742 0.32
'2009-05-11' 2.9020 0.97
'2009-05-12' 2.9166 0.50
'2009-05-13' 2.9147 -0.07
'2009-05-14' 2.9700 1.90
'2009-05-15' 2.9768 0.23
sql:
REPLACE TABLE previous AS
SELECT * FROM chf
TRANSFORM:
Exchange_1 = prev_table[-1]
Exchange_2 = prev_table[-2]
Exchange_3 = prev_table[-3]
Exchange_4 = prev_table[-4]
Exchange_5 = prev_table[-5]
prev_table.append(Exchange)
del prev_table[0]
GLOBALS:
prev_table = [ None ] * 5 # the user can decide how far into the past to reach
sql previous_out:
select * from previous
print previous_out
Output:
When | Exchange | Diff | Exchange_1 | Exchange_2 | Exchange_3 | Exchange_4 | Exchange_5 | +----------+----------+-------+------------+------------+------------+------------+------------+-- 2009-05-04 | 2.9068 | 0.08 | None | None | None | None | None | 2009-05-05 | 2.8753 | -1.08 | 2.9068 | None | None | None | None | 2009-05-06 | 2.9134 | 1.33 | 2.8753 | 2.9068 | None | None | None | 2009-05-07 | 2.8649 | -1.66 | 2.9134 | 2.8753 | 2.9068 | None | None | 2009-05-08 | 2.8742 | 0.32 | 2.8649 | 2.9134 | 2.8753 | 2.9068 | None | 2009-05-11 | 2.902 | 0.97 | 2.8742 | 2.8649 | 2.9134 | 2.8753 | 2.9068 | 2009-05-12 | 2.9166 | 0.5 | 2.902 | 2.8742 | 2.8649 | 2.9134 | 2.8753 | 2009-05-13 | 2.9147 | -0.07 | 2.9166 | 2.902 | 2.8742 | 2.8649 | 2.9134 | 2009-05-14 | 2.97 | 1.9 | 2.9147 | 2.9166 | 2.902 | 2.8742 | 2.8649 | 2009-05-15 | 2.9768 | 0.23 | 2.97 | 2.9147 | 2.9166 | 2.902 | 2.8742 |
Wykonując kod Gythona w bloku TRANSFORM można też w ywołać dowolne polecenie SQL. Służy do tego obiekt __connection__, zdefiniowany w środowisku Gythona.
Przykład 33.10. Kod SQL w klauzuli TRANSFORM, część 1
# input table:
table 'TAB7':
a b c
'aaa' 10 10
'bbb' 42 20
'ccc' -20 45
'ddd' 35 76
'eee' 88 99
'fff' 31 52
sql:
REPLACE TABLE TAB8 AS
SELECT * FROM TAB7
TRANSFORM:
cur.execute('insert into TAB9 values(?, ?)', (__rowNumber__, b))
GLOBALS:
cur = __connection__.cursor()
cur.execute('drop table if exists TAB9')
cur.execute('create table TAB9(var1 int, var2 int)')
sql a1:
SELECT * FROM TAB8
print a1
sql a2:
SELECT * FROM TAB9
print a2
Output:
a | b | c | +---+-----+----+-- aaa | 10 | 10 | bbb | 42 | 20 | ccc | -20 | 45 | ddd | 35 | 76 | eee | 88 | 99 | fff | 31 | 52 | var1 | var2 | +----+------+-- 0 | 10 | 1 | 42 | 2 | -20 | 3 | 35 | 4 | 88 | 5 | 31 |
Przykład 33.11. Kod SQL w klauzuli TRANSFORM, część 2
# input table:
table 'TAB7':
a b c
'aaa' 10 10
'bbb' 42 20
'ccc' -20 45
'ddd' 35 76
'eee' 88 99
'fff' 31 52
sql:
REPLACE TABLE TAB8 AS
SELECT * FROM TAB7
TRANSFORM:
if b <= 40:
var1 = a1
else:
var1 = a2
GLOBALS:
cur = __connection__.cursor()
cur.execute('select count(*) from TAB7')
a1 = cur.fetchone()[0]
cur.execute('select count(*) from TAB7 where c > 50')
a2 = cur.fetchone()[0]
DROP a1, a2
sql TAB8_out:
SELECT * FROM TAB8
print TAB8_out
Output:
a | b | c | var1 | +---+-----+----+------+-- aaa | 10 | 10 | 6 | bbb | 42 | 20 | 3 | ccc | -20 | 45 | 6 | ddd | 35 | 76 | 6 | eee | 88 | 99 | 3 | fff | 31 | 52 | 6 |
Klauzuli TRANSFORM nie można wykorzystywać, jeżeli w nadrzędnej instrukcji SQL występują poniższe elementy:
Blok TRANSFORM może być wykorzystywany przy budowie podkwerend i widoków, jednak wykonanie kwerend tego rodzaju zajmie więcej czasu z uwagi na to, że konieczne będzie utworzenie tymczasowej tabeli danych.