ExcelでSQLを使う-010: SQL 重複行の除去 ー キーワード DISTINCT
|
|
|
動物のお医者さん 第２章 |
西根家にはいろいろな動物が出入りする。ヒヨちゃんに輪をかけた乱暴者のメンドリコンビ。思いっきりワガママな仔犬スコシ。酒飲みのスナネズミ。緊急治療に飛び込む近所の人々ー。そいて、極めつきは、ドイツに住む音楽家・ハムテルの両親の登場！なにごとにも動じないマイペースなハムテルの性格形成は、この環境のもとでなされたのだった！？３年生も終わりに近づき、漆原教授の講座を選ぶハムテルの未来は明るいのかー？（同書の帯より）
◇◇◇◇◇
本稿では、SELECT文を高度に活用するための複雑な文について説明します。
重複行の除去
SELECT文にキーワード DISTINCT を記述すると、問合せによって検索した行から重複した行を除去します。
SELECT DISTINCT ＜選択対象の並び＞
例えば、次の SELECT文を実行すると、エクセルの表＜東京都郵便番号＞の列F8から重複した市区を除いてユニークな市区だけを検索します。
SELECT DISTINCT F8 FROM [東京都郵便番号$A1:I4000]
＜東京都郵便番号＞は、ネットからダウンロードしただけでは見出し列がありません。ですから、列名は”市区”ではなくて”F8”と書きます。また、5番目の引数は False(ヘッダー無し）を指定します。ちゃんと、列名を追加すれば、この限りではありません。
一応、念のために”F8”が市区に相当するのかを、冒頭の3行だけ読み込んで確認しておきます。
? DSelect("SELECT DISTINCT TOP 3 F8 FROM [東京都郵便番号$A1:I4000]",,chr(13),,False)
三宅島三宅村
三宅郡三宅村
結果、＜東京都郵便番号＞では ”三宅島三宅村”も市区に含まれていることが分かります。また、キーワード DISTINCT を使うとソートされることも分かります。全てを読み込んだ結果は、次のようになります。
? DSelect("SELECT DISTINCT F8 FROM [東京都郵便番号$A1:I4000]",,chr(13),,False)
三宅島三宅村
・・・・・
さて、一体、ユニークな市区は何件あったのでしょうか？。
? DLookup("SELECT COUNT(*) FROM (SELECT DISTINCT F8 FROM [東京都郵便番号$A1:I4000])",,False)
62
ここでは、集計関数について知ることがテーマではありません。知りたいのは、
× DLookup("SELECT COUNT(DISTINCT F8) FROM [東京都郵便番号$A1:I4000] ",,False)
という書き方が出来るかどうかです。
残念ながら Jet エンジンは、COUNT(DISTINCT FieldName) という書き方をサポートしていませんでした。ですから、 前述のような書き方をするしかありません。
なお、次の文は、一見すると正しいようですがユニーク列の集計値とは異なる値を得ます。注意が必要です。
? DLookup("SELECT COUNT(F8) FROM [東京都郵便番号$A1:I4000] GROUP BY F8",,False)
42
たかだかDISTINCT句のテストで一日を費やしました。性も根も尽き果てたので、本稿はここで終わります。次は、算術関数についてテストしつつ案内します。
【補足：Excelのフィルターと結果が違う問題について】
念には念を入れてということで、ユニークな市区の抽出結果を別シートに書きだしてみた。すると、61件という結果を得た。
? SQLWriter("SELECT DISTINCT F8 FROM [東京都郵便番号$A1:I4000]","New",,False)
True
それではと、エクセルのフィルターの機能を利用して重複列を省いてみた。すると、63件という結果を取得した。が、よくよく見ると、”あきる野市”が重複している。この重複を1とカウントすれば、結果は一致する。
SQLWriter関数が、”三宅島三宅村”を出力しなかったのは、ダウンロードした[東京都郵便番号]の不具合だった。”三宅島三宅村”を消して入力すると検索にひっかかった。こうして、無事、ユニークな市区は62件ということで一件落着した。
なお、エクセルのフィルターの機能は、先頭行を見出しと見做すようだ。知らなかった。
以上、補足しておく。
