[SQL]特定の文字コード領域を指定して文字の出現回数をランク付けする

Question

【環境】
SQL Server 2014

【目的】
ある顧客情報が格納されているテーブル（仮にCUSTOMER_INFOとします）から、使用された人名外字の回数をランキングで取得したいと思っています。
人名外字は、下記のような特定の文字コード領域に割り当てられています。
(例:0x8440～0x844F)

【テーブル構成(仮)】
必要カラムのみ抜き出して簡略化してますがだいたいこんな感じです。

[ORDER_NO] [char](13) NOT NULL,
[FAMILY_NAME] [varchar](20) NULL,  -- 姓
[FIRST_NAME] [varchar](20) NULL    -- 名

【期待する結果(例)】

1 , 髙, 100
2 , 﨑, 99
3 , 邉, 80
…
10, 齋, 20

下記SQLで指定文字の出現数を数えることは出来ると思うのですが、この方法だと外字の個数分だけの行数を書くことになってしまい、あまりスマートではないと思っています。

--例「あ」～「お」までの出現数を数える
SELECT
	SUM(LEN(FAMILY_NAME) - LEN(REPLACE(FAMILY_NAME, 'あ', ''))) 
		+ SUM(LEN(FIRST_NAME) - LEN(REPLACE(FIRST_NAME, 'あ', ''))) AS 'あ',
	SUM(LEN(FAMILY_NAME) - LEN(REPLACE(FAMILY_NAME, 'い', ''))) 
		+ SUM(LEN(FIRST_NAME) - LEN(REPLACE(FIRST_NAME, 'い', '')))AS 'い',
	SUM(LEN(FAMILY_NAME) - LEN(REPLACE(FAMILY_NAME, 'う', ''))) 
		+ SUM(LEN(FIRST_NAME) - LEN(REPLACE(FIRST_NAME, 'う', ''))) AS 'う',
	SUM(LEN(FAMILY_NAME) - LEN(REPLACE(FAMILY_NAME, 'え', ''))) 
		+ SUM(LEN(FIRST_NAME) - LEN(REPLACE(FIRST_NAME, 'え', ''))) AS 'え',
	SUM(LEN(FAMILY_NAME) - LEN(REPLACE(FAMILY_NAME, 'お', ''))) 
		+ SUM(LEN(FIRST_NAME) - LEN(REPLACE(FIRST_NAME, 'お', ''))) AS 'お'
FROM CUSTOMER_INFO

特定の文字コード領域を指定して出現回数をカウントするための、他のうまい方法があるようでしたら情報いただけるとありがたいです。

よろしくお願いいたします。

Answer 1

ぱっと思いついた方法です。

別にすべての外字を格納するテーブルを作ることができるなら以下のようにすれば希望しているものが作れそうです。

外字格納用テーブル

CREATE TABLE [dbo].[external_character] (
  chara varchar(2) NOT NULL
);

ここに集計対象の外字を流し込んでおきます。

集計用クエリ

SELECT
RANK() OVER(ORDER BY COUNT(*) DESC) AS ランキング,
external_character.chara 文字,
COUNT(*) AS 出現数
FROM external_character, CUSTOMER_INFO
WHERE (CUSTOMER_INFO.FAMILY_NAME + CUSTOMER_INFO.FIRST_NAME) LIKE ('%' + external_character.chara + '%')
GROUP BY external_character.chara

※一つのレコードに同じ外字が複数回入ってくることを考慮していません。
※RANK()はSQL2005以降です。
※重たいかもしれません。レコード数次第。

Comments

[masarusan24]

ご回答ありがとうございます。

RANK()関数は初めて知りました。
本番稼働中のシステムなのでテーブルの新規作成は出来ませんでしたが、現在未使用で近しい構成のテーブルがありましたので、そちらにデータを投入して試したところ期待通りの結果を得ることができました。

勉強になりました。
ありがとうございます！

[masarusan24]

今回のノウハウを下記にまとめさせていただきました。
http://qiita.com/masarusan24/items/7090a406a64aad4f3f1e

不適切な内容等ありましたら編集・削除しますのでご指摘ください。

[dupont_kedama]

qiitaの投稿については特に問題ありませんが、自分で書いた内容について指摘します。
まず、集計用SQL文で「external_character.chara 文字」としていますが他の列と同様に「external_character.chara AS 文字」とした方がよいですね。
それから「external_character」テーブルのDDLでインデックスを定義していません。同じ文字が入るのを許さない方がよいので、主キーにしてしまうか、せめてユニークインデックスをつけるべきでした。

[masarusan24]

確かにそのとおりですね。
ご指摘ありがとうございます。
該当箇所を修正しました。