FC2ブログ

CSVのデータをDBに投入する

忘れないようにメモしておくだけ。

[Budget] tblが既に存在している前提。
DBサーバーのCドライブ直下の「data」フォルダに「2020_budget.csv」が保存してある。

bulk insert [Budget]
from
'C:\data\2020_budget.csv'
with (
fieldterminator = ',',
rowterminator = '\n')


調べていて、fieldterminator、rowterminator という語にちょっと反応してしまった。
terminator = 終らせるもの。データの区切り文字と改行コードということ。
以上。

文字数を指定して切り取る

ふと思いついた。

このブログでは、「月別アーカイブ」というタイトルで、何年何月に何件の記事を投稿したかの履歴が出る。

2010年08月 (22)
2010年08月 (22)
2010年09月 (29)
2010年10月 (30)


これを、月ごとにデータを取りたいのだけれども、そこそこ長くやっていると、全部手打ちで入力するのも手間である。
それで、EXCELの計算式を使って取得する。
上記のものを全部コピーしてEXCELに貼り付けると、1行目ならA列に「2010年08月 (22)」すべてが入ることになる。

=MID(A1,11,2)


とすれば「22」を得ることができる…のだが、マメに更新していたのは過去のこと、最近では大体「2019年09月 (8)」という感じだから、そうなると「8)」が取得されてしまう。
それで、もう少しだけ凝った計算式にする。

年月の部分は形が固定だから、

=LEFT(A1,8)


で「2010年08月」が取得できる。
問題は、「(22)」から数値のみを取り出す方法。
数値が始まるのは「(」の次からなのだから、それが何文字目なのかを取得。

=MID(A2,FIND("(",A1)+1


次に、そこから何文字取得するかを計算する。

=FIND(")",A1)-FIND("(",A1)-1


「(22)」なら「2」が返って来る。「(8)」なら「1」。

これを組み合せると、

=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)


となる。
開始位置と、取得する文字数を計算しているわけである。
なお、ここでは月が「08月」のような形で必ず2桁あるので単純に左から8文字、で取得しているけれども、「8月」のようになっている場合なら、「月」の
位置を取得してそこまで、というふうにする必要がある。
とは言え、

=LEFT(A2,FIND("月",A1))


とするに過ぎない。

上記のものをテキストエディタに貼り付けて、「 (」をタブに置換して、「)」を削除してEXCELに貼り付けてもほぼ同じ結果を得ることはできるのだけれども、それだけでは記事のネタにはならない。

複数の条件を同時に満たす場合は除外する

複数の条件を同時に満たす場合は除外するSQL。

TEST_TBL の column1 とcolumn2 が両方とも「0」のレコードは除外したい。

select * from [TEST_TBL]
where ([column1] <> 0 or [column2] <> 0)


column1 が「0」以外、もしくはcolumn2 が「0」以外。つまりはどちらかに「0」以外が入っているものが対象になる。
言い換えれば、どちらも「0」が入っているレコードは対象外になる……はず。

理屈ではそうなのだとは思うのだけれども、何となく自信が持てずにいたら、優秀なSEの方が忙しい中わざわざ調べて教えてくれた。

select * from [TEST_TBL]
where not ([column1] = 0 and [column2] = 0)


column1 が「0」かつ column2 が「0」、つまり両方が「0」のもの【ではない】という条件句。

結果、先のSQLと同じレコード数が取得できたのだけれども、後のSQLの方が、ちょっとカッコいいし、間違いもなさそうな気がする。
ホントはどっちでも同じなのだろうけど…。

税込価格を計算する

税抜価格から税込価格を計算するSQL。

[Item_tbl] テーブルの [price] カラムに税抜価格が入っている。


select [price] * 1.08 as [taxinprice] from [Item_tbl]



以上出来上がり…で終わるとさすがに「ナメテンノカ!」という感じなので、もう少し書く。

このSQLが使えるのは今年の9月いっぱい。10月1日からは「1.08」を「1.1」に書き変えなければならない。が、10月1日にやらなければいけないことはほかにもたくさんあるだろう。
そこで、10月1日になったら自動で消費税率が変ってくれるようにする。
ただし、消費税率のマスタをDBに持っていることが前提。

[TAX_MASTER_tbl] テーブルに消費税率を持っている。


id/start_date/end_date/tax
1/1989-04-01/1999-03-31/0.03
2/1999-04-01/2014-03-31/0.05
3/2014-04-01/2019-09-30/0.08
4/2019-10-01/2099-12-31/0.10



今日時点での消費税率は、


select [tax] from [TAX_MASTER] where start_date <= getdate() and end_date>= getdate()



で取得できる。9月30日までは id「3」が、10月1日からはid「4」が取得できる。それを、税抜価格に掛けてやれば良いわけである。


select [price] * (1+(select [tax] from [TAX_MASTER] where start_date <= getdate() and end_date>= getdate()))as [taxinprice] from [Item_tbl]



もちろん、9月30日までは8%、10月1日からは10%というSQLも書けるけれども、そんなことをするくらいならベタで税率を書き換えた方が楽。

リンクサーバーは重い Part2

2つのDB(DB1、DB2)のそれぞれ2つのテーブルをJOINする。

select * from
DB1_TBL1 tb1
inner join
DB1_TBL2 tb2 on tb1.a = tb2.b
inner join
DB2_TBL3 tb3 on tb2.b = tb3.c COLLATE Japanese_CI_AS
inner join
DB2_TBL4 tb4 on tb3.c = tb4.d
where
tb1.e= WWWW
and tb2.f = XXXX
and tb3.g = YYYY
and tb4.ih= ZZZZ


が、場合によっては重くてパフォーマンスに問題が出ることがある。
そういう時、リンクサーバー側のデータ量を減らすことによって、改善する場合がある。

select * from
DB1_TBL1 tb1
inner join
DB1_TBL2 tb2 on tb1.a = tb2.b
inner join
(
select * from
DB2_TBL3 tb3
inner join
DB2_TBL4 tb4 on tb3.c = tb4.d
where
and tb3.g = YYYY
and tb4.h = ZZZZ
) DB2tb on tb2.b = DB2tb.c COLLATE Japanese_CI_AS
where
tb1.e = WWWW
and tb2.f = XXXX


リンクサーバー側をサブクエリ(DB2tb)にして、サブクエリ内で必要なデータだけに条件を絞ってしまうことによってデータ件数を減らすことができる。
むろん、著しく効果がある場合もあれば、ない場合もある。

( )で括られた中の文字だけを取得する

データ音痴が「簡単にできる」と安請け合いして来た難題の尻拭い。
( )で括られた中の文字だけを取得したい。

[COUNTRY_TBL] テーブルの [COUNTRY_NAME] カラムに「America(アメリカ)」とある。
このうち「アメリカ」だけを取得したい場合のSQL。

「America(アメリカ)」だけなら、

select substring(COUNTRY_NAME,9,4) from COUNTRY_TBL


で実現は可能だけれども、それだと先頭からの文字数と取得する文字数を指定しているだけだから、「Canada(カナダ)」だとしたら、「ナダ)」、「New Zealand(ニュージーランド)」なら「and(」になってしまう。これではまったく汎用性がない。

まずは、「(」が何文字目にあるかを取得する必要がある。

select charindex('(',COUNTRY_NAME)) from COUNTRY_TBL


これで取得できる値は「America(アメリカ)」なら「8」。

「(」から前の文字列を取得。

select left(COUNTRY_NAME',charindex( '(',COUNTRY_NAME)) from COUNTRY_TBL


これで取得できるのは「America(」。
この文字列は不要なものなので、「America(アメリカ)」からこれを削除する。

select replace(COUNTRY_NAME,(left (COUNTRY_NAME,charindex('(',COUNTRY_NAME))),'') from COUNTRY_TBL


「アメリカ)」を取得。
ここまでくればもう一歩、「)」をブランクに置換。

select replace(replace(COUNTRY_NAME,(left (COUNTRY_NAME,charindex('(',COUNTRY_NAME))),''),')','') from COUNTRY_TBL


「アメリカ」の取得に成功。

※もし「)」の後ろにも文字が入っているデータなら、「(」の処理と同じようなことを「)」でもしなければならない。
※括弧に半角、全角が混じっている場合、replaceしてどちらかに揃える必要がある。半角と全角で同じ処理をしても同じ結果は得られるけれども、複雑過ぎるからおススメできない。
※テーブル・カラムは実在のものではないので、記載したSQL自体は実際に流したものではない。流してエラーが出るようなら、括弧の数が合っていない可能性が高い。

指定の件数分だけupdateする

1本のupdate文で大量のレコードを更新する場合、全件を一括して更新すると時間も負荷も掛かってしまう。
それで、少しずつupdateしたいと思って、試しに

update top 1000 TBL set …


と書いてみたのだけれども、「'1000' 付近に不適切な構文があります。」というエラーが出て更新ができない。

それで、where句をselect文にしてその中にtop 1000を入れることも考えて、それはそれで可能なのだけれども、調べてみたらもっと簡単にできることが判った。

update top (1000) TBL set …


というふうに、件数を括弧で括るのみ。

ただし気を付けなければいけないのは、where句でset前の値を指定しておかないと、いつまで経っても最初の1000件だけが同じ値に只管上書かれ続けてしまうこと。
言わずもがなか…。

行番号のないデータに連番を振る。

[customer_code],[customer_name],[order_date] を保持している [sales_table] テーブルがあるとする。
同一の [customer_code] のレコードの中で [order_date] の古い順に行番号を振るSQL。

select
ROW_NUMBER() OVER(PARTITION BY [customer_code] order by [order_date] asc) as [RowNo],[customer_code],[customer_name],[order_date]
from
[sales_table]
order by [customer_code]

order by

1月も後半だけれども、改めて去年も大してブログを更新しなかったな、と思って、下書に入っている反故のエントリを多少手直しして上げることにした。
もとより反故だから、大したものではない。

こんなテーブルがあるとする。

[SAMPLE_TBL]

IdName
0001山田太郎
0002田中次郎
0003山田太郎
0004高橋三郎
0005高橋三郎
0006田中次郎
0007山田太郎
0008山田太郎
0009山田太郎
0010田中次郎

SQLで抽出するデータを並べ替える場合、言うまでもなく「order by」を使う。
が、この場合に「order by [Id]」を指定しても[Id]の順で並ぶだけだから、[Name]で並べ替えたい。

select * from [SAMPLE_TBL]
order by [Name],[Id]


抽出結果は、こんな感じ。

IdName
0001山田太郎
0003山田太郎
0007山田太郎
0008山田太郎
0009山田太郎
0002田中次郎
0006田中次郎
0010田中次郎
0004高橋三郎
0005高橋三郎

[Name]で並べ替えたとは言っても、何の順に並んでいるのかは良く判らない。
これを任意の順序に並べ替えたい場合、「order by」句にcase文を使うことができる。

select * from [SAMPLE_TBL] 
order by case [Name] when '高橋三郎' then 1 when '田中次郎' then 2 when '山田太郎' then 3 end,[ID]


IdName
0004高橋三郎
0005高橋三郎
0002田中次郎
0006田中次郎
0010田中次郎
0001山田太郎
0003山田太郎
0010山田太郎
0008山田太郎
0009山田太郎

ただそれだけ。反故になっていただけのことはある。

リンクサーバーは重い

SQLでリンクサーバーとJOINしようとすると、「equal to 操作での照合順序の競合を解決できません」というようなエラーが出る。
その場合、JOINの条件たる「on a.XXXX=b.XXXX」の後におまじないのように

COLLATE JAPANESE_CS_AS_KS_WS


と書くと解決できる。異なるDBの照合順序を、SQLサーバーに教えてあげるわけである。
おまじないだから意味を覚える必要はないのだけれども、「CS」「AS」「KS」「WS」というのは、

「C」=大文字小文字
「A」=アクセント
「K」=ひらがなカタカナ
「W」=全角半角


で、それを「S」=区別する、ということらしい。ちなみに区別しないは「I」。

…のだが、如何せんこの照合順序の競合を一所懸命解決しようと頑張っているからリンクサーバーは重い。
が、そもそも、SQLサーバーにはデフォルトの照合順序があって、DBやテーブルの作成時に特に指定しなければ、「Japanese_CI_AS」になっているようである。
DBに設定されていない照合順序まで、こちらから指定して解決してあげる必要はないから、「COLLATE Japanese_CI_AS」と指定してあげさえすれば、競合は解決する。
そして、解決しようとする労力が減った分、軽くなる。

なお、DBに設定されている照合順序は、下記のSQLで確認することができる。

SELECT SERVERPROPERTY('collation')