FC2ブログ

指定の件数分だけ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')

Excelの小技(9)VLOOKUP応用編

たまに使うのだけれども、たまにしか使わないから忘れてしまうものを備忘のためにメモしておく。

ふつうのVLOOKUP関数。

=VLOOKUP(B:B,Sheet2!A:B,2,FALSE)


VLOOKUPは、参照先に該当する値がない場合、「#N/A」と表示されてしまう。
この場合、LOOKUPした値の合計を計算しようとしても、数値ではないものが入っているからSUMすることができない。

それを回避するには、「ISERROR」を使う。

=IF(ISERROR(VLOOKUP(B:B,Sheet2!A:B,2,FALSE)), 0, VLOOKUP(B:B,Sheet2!A:B,2,FALSE))


「VLOOKUP(B:B,Sheet2!A:B,2,FALSE)」の結果がエラー(「#N/A」)だったら「0」を入れろ、そうでなければ「VLOOKUP(B:B,Sheet2!A:B,2,FALSE)」の結果を入れろ、ということ。

日付の指定

SQLで、where句に日付を指定する場合についてのちょっとしたメモ。

データ作成日([create_datetime])が2016年8月のものを抽出したいとする。なお、このカラムはdatetime型であることとする。

where
[create_datetime] >= '2016-08-01' and
[create_datetime] <= '2016-08-31'


このように条件を指定すれば、2016年8月に作成されたデータがすべて抽出できるかというと、そういうわけではない。
2行目FROM「'2016-08-01'以上」の方は問題ないのだけれども、3行目TO「'2016-08-31'以下」は、実際には「'2016-08-31 00:00:00 000'以下」だから、この時刻を1msでも越えたら範囲外になる。だから、この条件では、事実上、「'2016-08-31'」のデータは抽出されない。

3行目TOを変更して、

where
[create_datetime] >= '2016-08-01' and
[create_datetime] < '2016-09-01'


とすれば、「'2016-09-01'未満」となるから8月分のデータはすべて抽出できる。だからこれで良しと言えないことはないのだけれども、何かのツールに組み込んでこれをパラメーターにする場合、ユーザーに常にプラス1日で入力して貰うことには、理解を得がたい場合もあるだろう。

そこで、次のようなSQL文を書く人もいる。

where
[create_datetime] >= '2016-08-01' and
convert(date,[create_datetime]) <= '2016-08-31'


[create_datetime]の値をdate型に変換すれば、8月31日のデータはすべて'2016-08-31'という形になるから、この条件で8月分のデータはすべて抽出できることになる。

僕も、最初の内はこういう書き方をしていたのだけれども、これだと[create_datetime]をすべてdate型に変換した上で、その変換結果の中から該当日付のデータを抽出することになるので、処理としては重くなる。[create_datetime]にインデックスが張られれていたとしても、convert(date,[create_datetime])に対してそのインデックスが効くわけではない。

それで、こんな書き方をするようにした。

where
[create_datetime] >= '2016-08-01' and
[create_datetime] < dateadd(day,1,'2016-08-31')


「'2016-08-31'プラス1日(='2016-09-01')未満」という条件になるから、これで8月分のデータがすべて抽出できる。前のSQLと違って、検索するカラムのデータに対して型変換を掛けているわけではないから、処理が無用に重くなるということもないし、毎回1日足す必要がないからユーザーフレンドリーでもある。

各種変換

以前のエントリをちょっと見直していたら、昨年末、「今度はSQLのメモでも書いてみるか」などと書いていた。すっかり忘れていたけれども、予告していたものならもう少し書かないとカッコ付かないので、書いておくことにする。ただし、書くごとにどんどん大したことがなくなって行くのは致し方ない。

簡単なんだけれども、すぐに忘れてしまってその都度検索してしまうSQLのメモ、その2。

--指定の値より大きい最小の整数を返す…切上げ
select ceiling( 1.11 ) --'2'

--指定の値より小さい最大の整数を返す…切捨て
select floor( 1.11 ) --'1'

--ステートメントを宣言
DECLARE @postalcode nvarchar(8) = '236-0044'

--指定のカラムの値の順序を指定して並べ替え
ORDER BY case [カラム] when N'鈴木' then 1 when N'岡本' then 2 when N'佐藤' then 3 else 4 end

--データの前後のスペースを取り除く。
--先頭
select ltrim(' あああああ ') --'あああああ '
--末尾
select rtrim(' あああああ ') --' あああああ'
--両方
select ltrim(rtrim(' あああああ ')) --'あああああ'
--全角のスペースを取り除く場合。
select ltrim(replace('   あああああ',' ',' ')) --'あああああ'

--改行コード、TABを削除--CHAR(13)=line feed(LF),CHAR(10)=carriage return(CR),CHAR(9)=TAB
select REPLACE(REPLACE(REPLACE(N'カラム カラム',CHAR(13), ' '), CHAR(10), ''), CHAR(9), '') --'カラムカラム'

--文字列の一部を切り出す
substring('1234567890',6,4 -- '6789')

日付型の変換など

ひとつ書いたから、ついでにもう少し書いておく。あくまでも自分の備忘用だから、そんなにネタがあるわけではない。

簡単なんだけれども、すぐに忘れてしまってその都度検索してしまうSQLのメモ。
日付形式の変換あれこれ。

--現在日付・時刻を取得
select getdate() --'2016-08-24 00:00:00.000'

--現在日付を取得
select CONVERT(date,getdate()) --'2016-08-24'

--現在日付をyyyy/mm/dd形式で取得
select CONVERT(NVARCHAR,getdate(),111) --'2016/08/24'

--現在日付をyyyymmdd形式で取得
select CONVERT(NVARCHAR,getdate(),112) --'20160824'

--現在月をyyyymm形式で取得
select CONVERT(char(6),getdate(), 112) --'201608'

--前月月初日付を取得
select DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - 1, 0) --'2016-07-01 00:00:00.000'
select CONVERT(date,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - 1, 0)) --'2016-07-01'

--当月月初日付を取得
select DATEADD(MM, DATEDIFF(MM, -1, GETDATE()) - 1, 0) --'2016-08-01 00:00:00.000'
select CONVERT(date,DATEADD(MM, DATEDIFF(MM, -1, GETDATE()) - 1, 0)) --'2016-08-01'

--前月月末日付を取得
select DATEADD(MM, DATEDIFF(MM, -1, GETDATE()) -1, -1) --'2016-07-31 00:00:00.000'
select CONVERT(date,DATEADD(MM, DATEDIFF(MM, -1, GETDATE()) - 1, -1)) --'2016-07-31'

--前月月初日付をyyyy/mm/dd形式で取得
select CONVERT(NVARCHAR,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - 1, 0),111) --'2016/07/01'

--前月月末日付をyyyy/mm/dd形式で取得
select CONVERT(NVARCHAR,DATEADD(MM, DATEDIFF(MM, -1, GETDATE()) - 1, -1),111) --'2016/07/31'

縦持ちのデータを横持ちのデータに変換する

聊か唐突ではあるけれども、SQLについてのメモ。

縦持ちのデータを横持ちのデータに変換するSQL。
やり方を知って、案外簡単なことにちょっと感心したので、備忘のために書き付けておく。
何のことだかさっぱり判らないという方は、EXCELの「形式を選択して貼り付け」→「行列を入れ替える」と似たようなものだと思えばイメージを摑みやすいかもしれない。もとより「かもしれない」程度の例えだが。
もっとも、SQLを知らなかったからと言って日常生活に差し支える恐れは一切ないから、イメージが摑めなくても何の問題もないことは言うまでもないし、もし摑めたとしてもどうということもない。

たとえば、こんなデータがあるとする。

[SAMPLE_tbl]TBL
Sales_NoLine_NoSell_Item
00011いちご
00012みかん
00013なし
00014りんご
00021いちご
00022みかん
00023りんご
00024ぶどう
00031なし
00032ぶどう
00033みかん
00034りんご
00041なし
00042りんご
00043もも
00044みかん
00051もも
00052もも
00053もも
00054もも

伝票0001でいちご・みかん・なし・りんごが、伝票0002でいちご・みかん・りんご・ぶどうが…売れたという情報を縦持ちで持っているテーブル([SAMPLE_tbl])である。
これを、伝票番号([Sales_No])ごとの横持ちのデータとして抽出したい。

こんなものである。

Sales_NoSell_Item1Sell_Item2Sell_Item3Sell_Item4
0001いちごみかんなしりんご
0002いちごみかんりんごぶどう
0003なしぶどうみかんりんご
0004なしりんごももみかん
0005もももももももも

下記のようなSQL文で、抽出することができる。

select
 [Sales_No],
 max(case [Line_No] when 1 then [Item] end) as [Sell_Item1],
 max(case [Line_No] when 2 then [Item] end) as [Sell_Item2],
 max(case [Line_No] when 3 then [Item] end) as [Sell_Item3],
 max(case [Line_No] when 4 then [Item] end) as [Sell_Item4]
from
[SAMPLE_tbl]
group by [Sales_No]


「case [Line_No] when 1 then [Item] end~case [Line_No] when 4 then [Item] end」の部分で、「Sales_No = 0001」を例に言うと、

Sales_NoSell_Item1Sell_Item2Sell_Item3Sell_Item4
0001いちごNULLNULLNULL
0001NULLみかんNULLNULL
0001NULLNULLなしNULL
0001NULLNULLNULLりんご

というデータが抽出される。
それを「Sales_No」でgroup化して「Sell_Item1~4」のmaxを取ると、値の入っているものが取られて(NULLが省かれて)上記のようなデータになるわけである。

なるほどな、と思った次第。

Excelの小技(8)オートフィル

オートフィルという機能がある。連続する数字や文字を、自動的に入力する機能である。
到底「技」と呼ぶべくもない至極当り前のものではあるが、知っているのと知らないのとでは、効率が断然違う。

使い方としては、たとえば、一番上のセルに「1月」と入力して、

autofill

カーソルをセルの右下に合わせて「+」印が出るようにした状態でドラッグする。

autofill

と、

autofill

連続した月のセルが出来上る。
「+」印が赤いのは、判りやすくするためであって、実際には色は変わらない。

なお、数字だけの場合、一番上のセルに「1」と入れて引っ張るとすべてのセルが「1」になってしまうので、「1 2 3…」という連番を振りたい場合には「CTRL」を押しながらドラッグする。

さて、冒頭で「数字や文字」と書いたように、オートフィルは文字にも有効である。
「月曜日」と入力して、同様にドラッグすると、以下のようになる。

autofill

もちろん連続したことばのないものは、当然連続しない。

「田中」は、

autofill

あくまでも「田中」であるが、一々入力しなくとも、ドラッグした限りのセルが「田中」になる。

autofill

次に、作業が完了している月に「完」、完了していない月に「未」と入れることにする。
現時点ではまだ完了している月がなく、すべてに「未」と入れたい。こういう場合に、オートフィルが役に立つ。
そこで、一番上のセルに「未」と入れて、

autofill

オートフィル!

autofill

…ありゃ?!

いや、ただそれだけ。