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

人名外字の使用頻度をランク付けする方法をメモします。

環境

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文の生成自体も大変だし、めちゃくちゃ重いクエリとなるのであまりよろしくないです。

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

RANK()関数によるランク付け

すべての外字を格納するテーブルを別に作ることができるならば、以下のようにすれば期待する結果が得られます。

外字格納用テーブル

SQL

CREATE TABLE [dbo].[external_character] (
  chara varchar(2) NOT NULL ,
  CONSTRAINT [pk_external_character] PRIMARY KEY CLUSTERED 
  (
    chara
  )
);

あらかじめこのテーブルに集計対象の外字を流し込んでおきます。

集計用クエリ

SQL

SELECT
RANK() OVER(ORDER BY COUNT(*) DESC) AS ランキング,
external_character.chara AS 文字,
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()はSQL Server 2005以降で使用可能です。
レコード数次第で重たくなる可能性がありますので注意してください。