Teradata IN & regexp_split_to_table

TeradataのSQLでin句の中をパラメータとして文字列として受け取る小技を紹介したいと思います。

まずはデータを準備します。

drop table t1
;
create table t1(c1 int, c2 varchar(1))
;
insert into t1 values (1, 'a')
;
insert into t1 values (2, 'b')
;
insert into t1 values (3, 'c')
;
select * from t1
;
c1, c2
1,  a
2,  b
3,  c

上記のテーブルで1行目と3行目のレコードをin句で取得したい場合、以下のようなクエリになるかと思います。

select
*
from t1
where c2 in ('a', 'c')
order by 1
;
c1,c2
1, a
3, c

このカッコ内の文字列をパラメータとして受け取る際に、IFの都合でシングルクォートが自動でエスケープされてしまうなんてことがあるかもしれません。以下のようなクエリが渡された場合、Teradataはシンタックスエラーになります。

select
*
from t1
where c2 in (''a'', ''c'')
;

Teradataに詳しい方はoreplaceでシングルクォート2つを1つにすれば…と考えるかもしれませんが、この方法だとひとつの文字列として見なされてしまうので、レコードがヒットしなくなります。

select
*
from t1
where c2 in (oreplace('''a'', ''c''', '''''', ''''))
;

そこで登場するのがregexp_split_to_tableというマイナーな関数です。split_to_tableとあるように文字列をテーブルのように縦に分割してくれます。

select
*
from table (regexp_split_to_table(1,'''a'', ''c''', ',', 'i')
 returns (id integer, tokennum integer, token varchar(10) character set unicode)) as dt
;
id, tokennum, token
1, 1, ‘a’
1, 2, ‘c’

必要なのはtokenカラムなのでこれをtrimで整形してin句の条件にしてあげればできあがりです。

select
*
from t1
where c2 in (
 select
 trim(both '''' from trim(both ' ' from token)) as token
 from table (regexp_split_to_table(1,'''a'', ''c''', ',', 'i')
  returns (id integer, tokennum integer, token varchar(10) character set unicode)) as dt
)
order by 1
;

c1,c2
1, a
3, c

regexp_split_to_tableは本来こういった使い方をするための関数ではありませんが、こんな関数もあったなと覚えておくといつか役に立つかもしれないですし、立たないかもしれません👻

Jupyteradata

Jupyter NotebookからTeradataに接続して色々試し(修行)中です。
横持ちのデータを綺麗に可視化する方法は分かったんですが、縦持ちのテーブルで例えば折れ線グラフで複数の線を描く時に、どうするのか(何の線かを定義するようなオプション)がPandas, Bokeh, Plotlyのどれも見つからず…。むむむむむ。

%matplotlib inline
import matplotlib
import matplotlib.pyplot as plt
import teradata
import pandas as pd
import numpy as np

plt.style.use('ggplot')
font = {'family' : 'meiryo'}
matplotlib.rc('font', **font)

udaExec = teradata.UdaExec (appName="Teradata", version="1.0", logConsole=False)
session = udaExec.connect(method="odbc", dsn="server name", username="user name", password="******");
cursor =  session.cursor();

sql = '''
select
 vproc
,currentperm
,peakperm
from dbc.tablesize
where
databasename= 'dbc'
and tablename = 'AccessRights'
;
'''
df = pd.read_sql(sql, session)
df.plot.bar(x=['Vproc'], y=['CurrentPerm', 'PeakPerm'], alpha=1.0, figsize=(12,3))
plt.title(u'Skew', size=16)

from bokeh.plotting import figure
from bokeh.io import output_notebook, show

p = figure(title="Bokeh Graph", x_axis_label='x', y_axis_label='y')
p.vbar(df['Vproc'], top=df['CurrentPerm'], width=0.2, bottom=0, color="#CAB2D6")
show(p)