バーコード(JANコード)のチェックディジットをSQLとPL/pgSQLで計算してみた
バーコードを大量に生成する必要に迫られたので備忘録。
概要
バーコードといっても複数の種類があり、主に日本国内で見かけるのはJANコードと呼ばれる13桁または7桁の番号です。
そのどちらも、最後の1桁はチェックディジェットと呼ばれ、残りの1桁1桁からモジュラス10/ウェイト3といった方法で計算して求めます。
今回、その「モジュラス10/ウェイト3」をSQLとPL/pgSQLで再現しました。
SQL
select
a.jan,
case when a.ck = 10 then '0'::text else a.cd::text end as cd
from(
select
jan,
10 - mod(
case when length(jan) = 12 then
substr(jan, 12, 1)::integer * 3
+ substr(jan, 11, 1)::integer * 1
+ substr(jan, 10, 1)::integer * 3
+ substr(jan, 9, 1)::integer * 1
+ substr(jan, 8, 1)::integer * 3
+ substr(jan, 7, 1)::integer * 1
+ substr(jan, 6, 1)::integer * 3
+ substr(jan, 5, 1)::integer * 1
+ substr(jan, 4, 1)::integer * 3
+ substr(jan, 3, 1)::integer * 1
+ substr(jan, 2, 1)::integer * 3
+ substr(jan, 1, 1)::integer * 1
when length(jan) = 7 then
substr(jan, 7, 1)::integer * 3
+ substr(jan, 6, 1)::integer * 1
+ substr(jan, 5, 1)::integer * 3
+ substr(jan, 4, 1)::integer * 1
+ substr(jan, 3, 1)::integer * 3
+ substr(jan, 2, 1)::integer * 1
+ substr(jan, 1, 1)::integer * 3
else null end
, 10) as cd
from jans) as a;
PL/pgSQL
create or replace function calc_cd (text) returns text as $$
declare
x integer := 0;
len_code integer = length($1);
begin
if len_code <> 12 and len_code <> 7 then
return null::text;
end if;
for i in 1..len_code loop
case when (len_code - (i - 1)) % 2 = 1 then
x := x + substr($1, i, 1)::integer * 3;
else
x := x + substr($1, i, 1)::integer * 1;
end case;
end loop;
x := 10 - x % 10;
case when x = 10 then
return '0'::text;
else
return x::text;
end case;
end
$$ language plpgsql
returns null on null input
parallel safe;
SQLは恐ろしいほど力業だけども、PL/pgSQLはそれっぽく書けたかもしれない。
参考元
テーブル結合にLIKE句を用いて結合条件を部分一致としてみた結果と問題点
従業員の住所から全国地方公共団体コードを特定する作業を行った際に思い付きでやった方法が意外とうまくハマったのでメモ。
〇目的:従業員の住所から全国地方公共団体コードを割り当てる
〇テーブル定義
-- employee address
create table emp_addr(
emp_code integer,
addr text);
-- lg code https://www.soumu.go.jp/main_content/000730858.xlsxcreate table lg_code_list(
lg_code integer,
pref_name text, -- 都道府県名
muni_name text, -- 市町村名
pref_kana text,
muni_kana text);
〇実際のコード
select * from emp_addr as a
left outer join lg_code_list as b
on a.addr like b.pref_name || b.muni_name || '%';
たったこれだけで部分一致によるテーブル結合が実現します。
問題を回避する
ただし、住所情報に欠落や誤りがある場合は個別対応が必要です。
元データを訂正できればよいですが、できない場合は以下の通りとすることもできます。
〇特定の条件を結合させないケース
select * from emp_addr as a
left outer join lg_code_list as b
on a.addr like b.pref_name || b.muni_name || '%'
and case when b.pref_name || b.muni_name = '東京都町田市' then false
else true end;
〇特定の条件を他の条件へ置き換えるケース
select * from emp_addr as a
left outer join lg_code_list as b
on a.addr like
case when b.pref_name || b.muni_name = '東京都町田市' then '神奈川県町田市%'
else b.pref_name || b.muni_name || '%' end;
EXISTS記念日なので他のコードと実行計画を比較してみた
皆様どうもこんにちは。会計ソフトに起票するよりSQLばかり書いている経理財務部のみおつくし0800です。
ところで。今日は私が生まれて始めてSQLのEXISTS句でエラーも出ず求めるデータセットを出力できた日となりました。
もう嬉しくて興奮冷めやらぬ間に書き上げてしまおうと、このチラシの裏にすらもったいないほど下らないEXISTS句の書き方、そして実行計画をさらっと眺めた結果をまとめておきます。
※特に断りがない限り、すべてPostgreSQLで実行・検証しています。
取り急ぎ、どうやって書けば良いの?
SELECT col1
FROM tab1
WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
EXISTS句の中で最も重要なのはサブクエリ内のWHERE句です。EXISTS句は存在すればTRUEを返す、つまりWHERE句がTRUEと評価されるためデータセットに現れます。
そのため、EXISTS句を有効に活用するにはメインクエリとサブクエリを紐付けるような条件が必要です。私はこの部分をすっかり見落としており、まるでIN句のように使おうとして泥沼にハマっていたのです。ANSI結合でもこういった表記しましたよね?
他にも書けるIN句とINNER JOIN
EXISTS句は存在を判定する、とよく表記されますがそれならばIN句やINNER JOINでも書けるのではないか?と思考が進むかと思います。上記のクエリはそれぞれ以下のようにも表現できます。
select col1
from tab1
where col2 in (select col2 from tab2);
select col1
from tab1
inner join tab2
using(col2);
上記は同じデータセットを出力するはずですが、パフォーマンスが異なるようです。
EXISTS句の真価は NOT EXISTS かもしれない
実は私が今回生まれて始めて成功したのもNOT EXISTS句でありました。
もしかすると、EXISTS句よりもNOT EXISTS句の方がイメージしやすいのかもしれません。せっかく引用したので上図の左側の上から2番目のクエリをNOT EXISTS句を用いて表現したいと思います。
SELECT <select list>
FROM TableA A
WHERE NOT EXISTS
(SELECT 1
FROM TableB B
WHERE A.Key = B.Key);
自己満で終わらせないために実行計画で検証してみる
NOT EXISTS句においても実行計画をもとに検証してみます。
今回はちょうど手元に170万行ぐらいのテーブルがあったので、そこから以下の条件で抽出してみます。
デーブル:deposit
カラム :拠点名(brand_name::text)、入金日(deposit_date::date)
求めるデータセット:今年度(> 22-03-31)になって初めて入金のあった拠点におけるその年月
条 件:前年度以前に取引がなかった拠点における入金日の最小値から年月を取り出す
○NOT EXISTSを用いたクエリ
select
brand_name,
extract(year from min(deposit_date)) as deposit_year,
extract(month from min(deposit_date)) as deposit_month
from deposit as a
where a.deposit_date > '2022-03-31'
and not exists(
select 1 from deposit as b
where b.deposit_date < '2022-04-01'
and a.brand_name = b.brand_name)
group by brand_name
order by extract(year from min(deposit_date)),
extract(month from min(deposit_date));
○LEFT OUTER JOINとWHERE句を用いたクエリ
select
a.brand_name,
extract(year from min(a.deposit_date)) as deposit_year,
extract(month from min(a.deposit_date)) as deposit_month
from deposit as a
left outer join deposit as b
on a.deposit_date > '2022-03-31'
and b.deposit_date < '2022-04-01'
and a.brand_name = b.brand_name
where b.brand_name is null
group by a.brand_name
order by extract(year from min(a.deposit_date)),
extract(month from min(a.deposit_date));
○EXPLAINの結果
・NOT EXISTS
Sort (cost=230432.11..230432.11 rows=1 width=98)
・LEFT OURT JOIN
Sort (cost=242222.35..242222.63 rows=111 width=98)
・(比較用)SELECT * FROM deposit
Seq Scan on deposit (cost=0.00..118605.26 rows=1709626 width=226)
このページによると、今回は主にcostとrowsの比較をすれば良さそう。
結果は火を見るより明らかですが、NOT EXISTSに関してはすぐに結果表示されたのに対してLEFT OUTER JOINは5分ほど経っても応答がなく正しいかどうかの検証もままならぬまま。。。
クエリの文法が正しいと仮定すれば、自己結合のコスト高を改めて学べたのかもしれません。
(おまけ)EXISTS句のサブクエリ内の列指定は無視されるのか?
例のあのRDBMSのドキュメントによるとサブクエリ内の列指定は無視するから何を指定しても良いとのこと。
ということでせっかくなので再び実行計画を比較したいと思います。
・NOT EXISTSのサブクエリ内の列指定を*とした実行計画
Sort (cost=230432.11..230432.11 rows=1 width=98)
なるほど、、PostgreSQLにおいてもEXISTS句のサブクエリで指定した列は無視される模様。
ただ、意味のないものを意味があるような表現をするのも私個人としてはいかがかな?とは思います。
今後の課題
ただのおめでたい記事で終わらせる訳にもいかないので、今後も継続して追えられればと考えています。
特に実行計画の理解は深めたいところ。特に今回の実行計画はEXPLAINで留まっていることや、検証後半のLEFT OUTER JOINにおいては遅すぎて取得すらできなかった点は非常に心残り。
将来的にあわよくば「EXISTS句を使え、のび太よ」みたいなWebページを公開できれば尚よし。