リンクサーバーは重い

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

…ありゃ?!

いや、ただそれだけ。

SCHOTT Glass

スマホくらい持っていないと学校からの連絡を受け取るのもままならないような時代だから、高校入学を期に、娘に iphone を持たせている。
購入したショップで、落とした時の衝撃から液晶を守るための必需品だと言わんばかりの勢いで保護ガラスを薦められた。自分が使うものであればそれに乗って買うことはまずないのだけれども、娘が使うのだからと思って言うなりに購入した。
で、先日娘のスマホを見たら見事にヒビが入っている。ずいぶん派手に落としたようだ。が、本体は無傷。ショップ店員の言うことも、満更嘘ではではなかったということである。むろん、本当は落とさないように気を付けるべきなのは言うまでもないのだが…。
本体には問題がないのだからそのままにしておいても良いのだけれども、それもさすがにかわいそうだと思って代わりのものを買うことにした。
前のと同じもので良いと思って探しているうちに、ふと眼に入ったものがある。
それは、箱に書かれた「SCHOTT Glass」の文字である。

POWER SUPPORT SCHOTT Glass for iPhone6(PYC-03)
SCHOTT
箱の謳い文句に、「柔軟性と強靭性を併せ持つ、最上級ドイツ製ガラスを採用」と書かれている。が、液晶保護ガラスなんて、所詮は消耗品である。この謳い文句を見なければ食指の動かないような人は、別段こんな割高なものを買う必要はない。
でも、ついふらふらと、心が揺れて買ってしまった。何せ、ショット・ガラスなんだから…。

いろいろなものを買い換えた(その1)

その2を書くかどうかは判らないけれども、とりあえずその1。

愛用していたNexus7が起動しなくなった。
むろん修理をしようとは思うのだが、タブレットがあることに慣れ切ってしまった今となっては、その間タブレットなしで生活するのも実に不便である。
近々Nexusの新型が発売されるという情報もあり、それでやや値下がりをしているようでもあるので、思い切って買い直すことにした。
Nexus7以外の選択肢もないではないのだが、それにしてはこの製品の完成度が高過ぎて、ほかのものには手が伸びない。使う人によっては不満があるのかもしれないけれども、僕の用途からすればNexus7で必要かつ十分なのである。それ以上のことはパソコンに任せて、余分なものを載せない方が、却って使いやすい。
噂の新型Nexusは8インチとも9インチともいう。それが本当だとすると、この絶妙なサイズを捨ててしまおうとする理由が、僕には理解できない。それでは小型のパソコンと大して違いがない。そして、パソコンだとしたら、AndroidOSでは用をなさないことが、現状では多々ある。
むろんタブレットもパソコンの一種に違いないのだけれども、タブレットは、大きさと操作性が相俟ってのタブレットだと思うのである。その観点から、同じNexus7を買うことにしたのである。
もっとも、僕の元々持っていた初期型のもの(2012年モデル)は、今では手に入らないので、必然的に2013年モデルになった。前と同じもので良いのに…と思っていたのだが、実際に手に取ってみると、ディスプレイのサイズは変わらないのに横幅が微妙に小さくなって、持ち易さは格段に上がっている。

…と、褒めては見たものの、今回買った2013年モデル、実は僕は使っていない。
使っているキーボードが2012年モデル専用のもので、2013年モデルには嵌めることができないから、キーボードまで買い換えなくてはいけなくなってしまう。2013年モデル自体には不満はないのだけれども、それが最大の理由で、結局、家内の2012年モデルと交換することにしたのである。家内は元々、別のもう少し大きめのキーボードを使っているから、どちらのモデルでも支障はない。

だから、2013年モデルの違いは、サイズのほかにはHDMI出力に対応した点くらいしか実感していない。カメラは使わないし、処理速度やストレージ容量もこれまで不満に感じたことがないのである。
タブレットの画面をテレビに映すのは必須の機能とは言えないけれども、できないよりは、できたほうが楽しい。
もっとも、そのための機器を併せて買った割には、YOUTUBEにアップされているオダギリジョーのCMを映すくらいしか、していないのだけれども…。

久しぶりのmagrex MK5000

先日、久しぶりにNexus7専用のBluetoothキーボードmagrex MK5000を使った。
久しぶりな理由は、しばらくの間物理的に使えなかったのである。
ある時、突然認識しなくなって、一旦ペアリングを解除してからやり直そうと思ったのだが、それでも認識されなかった。故障だとしても、修理に出すにしても買い直すにしても、価格的に微妙な製品である。
捨てる踏ん切りも付かず、しばらく放っておいたのだが、ふと思い立ってNexus7のOSをAndroid4.3にバージョンアップしてみたところ、何と無事、ペアリングに成功した。
その後、4.4.2にしても問題なし。根拠はないが、使えなくなったのは4.2のバグだったのかもしれない。捨てなくて良かった。

なお、以前使っていた時は、このキーボードとFC2ブログの管理画面の相性が悪く、文字の入力中、エンターも押していないのに無変換のまま確定されてしまい、再入力を余儀なくされることが多々あって、別のアプリで文章を作成してコピペしたりしていたのだが、バージョンアップ後はそれが完全に解消された。これも、4.2のバグだったのだろうか。

ちゃんと使えるとなれば、こんな便利なものはない。