　シェル版より1秒くらい速い。

成果物

github

$ run.sh
1  あまき   ひいろ   天毎木   秀彩  f
2  あわら   あきこ   湶 彩葵子   f
3  いえつか    はるき   家塚  青生  mc
4  いわがわ    ともひで    岩川  智偉  m
5  うえくさ    えいと   上草  慧愛  cm
6  うみふち    きみか   海渕  君佳  f
7  えびさわ    ゆみか   蛯沢  夕海夏   f
8  おおかわら さき  大川原   皐綺  cf
9  おぎしま    みふゆ   荻嶋  真冬  f
10 かじき   かなみ   加治木   佳並  f
11 かねま   てるみ   兼間  照己  c
12 かば  ひさたか    蒲 寿孝  m
13 からさわ    かいじ   唐澤  快治  m
14 こうと   とき  児戸  翔騎  m
15 こうはら    みつか   神原  舜禾  f
16 ことうげ    なつえ   小峠  なつ江   f
17 さいうち    かいや   歳内  届也  cm
18 しおのや    せいな   塩之谷   星雪  f
19 しなおか    なる  品岡  那瑠  c
20 しょうぼ    りむ  正保  理群  m
21 じんざい    そうた   神西  蒼詩  m
22 そのい   こうき   園井  晃紀  cm
23 たかた   とわ  鷹田  留和  cf
24 たかのうち このか   高野内   好桜  f
25 たかむき    ひさみ   高向  尚観  f
26 ただのぶ    かお  只信  禾緒  cf
27 たのうえ    みおな   田之上   巳央那   f
28 つじはた    わこう   辻畑  和晃  m
29 てらべ   るいな   寺部  琉衣奈   f
30 とのえ   むつき   外江  睦葵  c
31 なかのわたり  くにあき    中野渡   邦暁  m
32 なかわ   まお  中和  麻織  cf
33 にぐま   さちほ   二熊  紗千帆   f
34 のりつぐ    ささら   則次  早々良   f
35 ひがしだて ともえ   東舘  登萠  f
36 ほぼ  としあき    保々  俊菊  m
37 まつぞの    まさる   松園  了 mc
38 まつみどり ななせ   松緑  那々世   cf
39 みつま   いつき   三満  一汐  cf
40 よこすか    まなは   横須賀   愛果  f

$ time run.sh
...
real    0m0.569s
user    0m0.531s
sys 0m0.029s

結論

select ROW_NUMBER() over (order by L.Yomi,F.Yomi) as SN,
  L.Yomi,F.Yomi,L.Kaki,F.Kaki,F.Sex 
  from (
    select ROW_NUMBER() over (order by random(), Id) as R,
      Yomi,Kaki
    from LastNames 
    where Id in (
      select Id from LastNames 
      order by random() limit @NUM)
    ) as L 
  inner join (
    select ROW_NUMBER() over (order by random(), Id) as R,
      Yomi,Kaki,Sex 
    from FirstNames 
    where Id in (
      select Id from FirstNames 
      where sex in ('m','mc','cm','c','f','fc','cf') 
      order by random() limit @NUM)
    ) as F
  on L.R=F.R
  order by L.Yomi,F.Yomi;

　超長い……。窓関数も使っていて難しそうに見えるが、やっていることは単純。

試行

　前回のDBを使う。

　まずはランダムに取得する件数を変数にしてみる。.parameterドットコマンドを使う。

sqlite> .help parameter
.parameter CMD ...       Manage SQL parameter bindings
   clear                   Erase all bindings
   init                    Initialize the TEMP table that holds bindings
   list                    List the current parameter bindings
   set PARAMETER VALUE     Given SQL parameter PARAMETER a value of VALUE
                           PARAMETER should start with one of: $ : @ ?
   unset PARAMETER         Remove PARAMETER from the binding table

　取得件数を@NUMという変数に代入する。

.parameter init
.parameter set @NUM 5
select * from LastNames where Id in (select Id from LastNames order by random() limit @NUM);

1751|あつうら|厚浦
22540|しろでら|城寺
23564|すさ|周佐
35663|はまの|濱埜
40868|まない|真井

　名前も取得してみる。名前は性別で絞込したい。

select * from FirstNames where Id in (select Id from FirstNames where sex in ('m','mc','cm','c') order by random() limit @NUM);
select * from FirstNames where Id in (select Id from FirstNames where sex in ('f','fc','cf','c') order by random() limit @NUM);
select * from FirstNames where Id in (select Id from FirstNames where sex in ('c','mc','cm','fc','cf') order by random() limit @NUM);

3626|えいた|絵伊太|m
6398|かんと|貫人|m
16051|さくと|咲友|m
36160|たつのぶ|竜史|m
54489|ひろと|宏都|m

　なお、パラメータに指定できるのは値のみ。SQL文の一部を含めることはできない。（SQLインジェクション攻撃対策と思われる）　そのせいで以下のようなことができない。

.parameter set @SEX_M "'m','mc','cm','c'"
select * from FirstNames where Id in (select Id from FirstNames where sex in (@SEX_M) order by random() limit @NUM);

　一件も出力されない。おそらく'SEX'列の値が'm','mc','cm','c'という文字列と一致する行を取得しようとしていると思われる。残念だが、ここはシェルでSQL文字列を作成するしかないだろう。

　名字と名前のランダム取得したレコードを結合してみる。まずは積集合から。

.parameter init
.parameter set @NUM 5
select L.Yomi,F.Yomi,L.Kaki,F.Kaki from 
  (select * 
    from LastNames 
    where Id in (
      select Id 
      from LastNames 
      order by random() 
      limit @NUM)
    ) as L, 
  (select * 
    from FirstNames 
    where Id in (
      select Id 
      from FirstNames 
      where sex in ('m','mc','cm','c') 
      order by random() 
      limit @NUM)
    ) as F;

こがめ|こうし|小亀|孝資
こがめ|じょうすけ|小亀|承甫
こがめ|たかひろ|小亀|隆尋
こがめ|はやと|小亀|早翔
こがめ|いおん|小亀|依穏
ののぎ|こうし|野々木|孝資
ののぎ|じょうすけ|野々木|承甫
ののぎ|たかひろ|野々木|隆尋
ののぎ|はやと|野々木|早翔
ののぎ|いおん|野々木|依穏
はまお|こうし|濱生|孝資
はまお|じょうすけ|濱生|承甫
はまお|たかひろ|濱生|隆尋
はまお|はやと|濱生|早翔
はまお|いおん|濱生|依穏
ふくずみ|こうし|福住|孝資
ふくずみ|じょうすけ|福住|承甫
ふくずみ|たかひろ|福住|隆尋
ふくずみ|はやと|福住|早翔
ふくずみ|いおん|福住|依穏
やたか|こうし|谷高|孝資
やたか|じょうすけ|谷高|承甫
やたか|たかひろ|谷高|隆尋
やたか|はやと|谷高|早翔
やたか|いおん|谷高|依穏

　同じ行だけを結合させたい。行数を取得し、行数を条件にして内部結合すれば良さそう。

　行数は窓関数で取得できる。窓関数はSQLite3の3.25.0版から使えるようになった。

select L.Yomi,F.Yomi,L.Kaki,F.Kaki from 
  (select ROW_NUMBER() over (order by Id) as R, Yomi,Kaki 
    from LastNames 
    where Id in (
      select Id 
      from LastNames 
      order by random() limit @NUM
    )
  ) as L 
  inner join  
  (select ROW_NUMBER() over (order by Id) as R,Yomi,Kaki 
    from FirstNames 
    where Id in (
      select Id 
      from FirstNames 
      where sex in ('m','mc','cm','c') 
      order by random() 
      limit @NUM
    )
  ) as F
  on L.R=F.R;

うとう|たつき|右藤|樹大
きたおち|としお|北落|淑夫
くまさか|としはる|熊坂|俊遥
しおねり|なりひろ|塩練|斉衆
にいつま|りお|新妻|理緒

　性別も出す。

select L.Yomi,F.Yomi,L.Kaki,F.Kaki,F.Sex 
from (
  select ROW_NUMBER() over (order by Id) as R,Yomi,Kaki 
  from LastNames 
  where Id in (
    select Id 
    from LastNames 
    order by random() 
    limit @NUM
  )
) as L 
inner join (
  select ROW_NUMBER() over (order by Id) as R,Yomi,Kaki,Sex 
  from FirstNames 
  where Id in (
    select Id 
    from FirstNames 
    where sex in ('m','mc','cm','c','f','fc','cf') 
    order by random() 
    limit @NUM
  )
) as F
on L.R=F.R;

せきがみ|おうすけ|関上|旺右|m
ときさき|きすけ|時崎|樹資|m
のこ|ひでなが|野古|英祥|m
みちづか|あやみ|満束|紋未|f
やまだいら|りつ|山平|李津|c

　ところで、このままだと名字と名前の組合せが偏ってランダムになっていない。Id値が低い名字と名前が組合せになりやすくなってしまう。ROW_NUMBER() over (order by Id)によりId順でソートされた結果、行番号が定まるため。かといって窓関数からover句を外すことはできない。

　というわけで、ROW_NUMBER() over (order by random(), Id)にした。ソートの第一キーにrandom()値を指定することでランダム順になる。万一ランダム値が完全同一のときは第二キーIdでソートする。

select L.Yomi,F.Yomi,L.Kaki,F.Kaki,F.Sex 
from (
  select ROW_NUMBER() over (order by random(), Id) as R,Yomi,Kaki 
  from LastNames 
  where Id in (
    select Id 
    from LastNames 
    order by random() 
    limit @NUM
  )
) as L 
inner join (
  select ROW_NUMBER() over (order by random(), Id) as R,Yomi,Kaki,Sex 
  from FirstNames 
  where Id in (
    select Id 
    from FirstNames 
    where sex in ('m','mc','cm','c','f','fc','cf') 
    order by random() 
    limit @NUM
  )
) as F
on L.R=F.R;

あきなが|ふみ|秋永|歩弥|cf
つねとし|みおか|常俊|深香|f
みくり|ゆうご|三厨|勇護|m
にいのみ|れいか|新家|莉叶|f
つば|しゅんと|鍔|駿友|m

　名前を辞書順にソートして出席番号を与える。

select ROW_NUMBER() over (order by L.Yomi,F.Yomi) as SN,
  L.Yomi,F.Yomi,L.Kaki,F.Kaki,F.Sex 
from (
  select ROW_NUMBER() over (order by random(), Id) as R,Yomi,Kaki 
  from LastNames 
  where Id in (
    select Id 
    from LastNames 
    order by random() 
    limit @NUM
  )
) as L 
inner join (
  select ROW_NUMBER() over (order by random(), Id) as R,Yomi,Kaki,Sex 
  from FirstNames 
  where Id in (
    select Id 
    from FirstNames 
    where sex in ('m','mc','cm','c','f','fc','cf') 
    order by random() 
    limit @NUM
  )
) as F
on L.R=F.R
order by L.Yomi,F.Yomi;

1|からと|せしる|唐渡|聖志留|f
2|きりふ|さえ|切封|伎絵|f
3|たか|こころ|隆|優|cf
4|みょうせき|まなき|明関|愛幸|m
5|わが|そうま|和賀|掃摩|m

　じつは窓関数を使っているため、最奥のサブクエリを省略できる。だが、実行速度が遅い。(0.580s→1.369s)

select ROW_NUMBER() over (order by L.Yomi,F.Yomi) as SN,L.Yomi,F.Yomi,L.Kaki,F.Kaki,F.Sex 
from (
  select ROW_NUMBER() over (order by random(), Id) as R,Yomi,Kaki 
  from LastNames limit @NUM
) as L 
inner join (
  select ROW_NUMBER() over (order by random(), Id) as R,Yomi,Kaki,Sex 
  from FirstNames limit @NUM
) as F
on L.R=F.R
order by L.Yomi,F.Yomi;

1|うぶさわ|すずは|生澤|紗花|f
2|じんぐうじ|かずたか|神宮寺|一昂|m
3|つるほ|よしいえ|鶴保|義家|m
4|なや|かずき|名屋|和城|cm
5|ますもり|せいしろう|増盛|惺志朗|m

課題

男女比1:1にしたい
- 男: m,mc,cf,c
- 女: f,fc,cf,c
- 奇数のときは全性別からランダムに1件取得する
性別をm,fのいずれかで出力したい
- c: ランダムでm,fのいずれかにする
- mc,cm: mにする
- fc,cf: fにする

所感

長所
- 実行速度がシェル版より1秒くらい速い
短所
- SQL文が難解に見える
- 窓関数はSQLite 3.25.0以降でないと使えない
- TSVファイル版より3.75倍くらいファイルサイズが多い

対象環境

2020-09-24
Raspbierry pi 4 Model B
Raspbian buster 10.0 2019-09-26 ※
bash 5.0.3(1)-release 学習まとめ
SQLite 3.33.0 ※ 学習まとめ

$ uname -a
Linux raspberrypi 4.19.97-v7l+ #1294 SMP Thu Jan 30 13:21:14 GMT 2020 armv7l GNU/Linux

やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

SQLite3で日本人のフルネームをランダムに取得する

成果物

結論

試行

課題

所感

対象環境