2020-12-24

BigQueryの標準SQLでGROUP_CONCATしたいときはSTRING_AGG

BigQueryでGROUP BYした項目を文字列結合したいとき、レガシーSQLでは`GROUP_CONCAT`を使ってました。

標準SQLにはなく、検索しても見つけづらかったのでメモ。

結論

STRING_AGGが使えます。

STRING_AGG([DISTINCT] expression [, delimiter] [ORDER BY key [{ASC|DESC}] [, ... ]] [LIMIT n])
[OVER (...)]

標準 SQL の式、関数、演算子 | BigQuery | Google Cloud

DISTINCTで重複除けるし、ORDER BYもここでできちゃうので、ID順とかで結合したいときこれだけでできてしまって超便利

例

こんなデータの場合、

WITH testData AS (
  SELECT 1 AS id, '赤身' as type,  'ブリ' AS name UNION ALL
  SELECT 2, '赤身', 'いわし' UNION ALL
  SELECT 3, '赤身', 'アジ' UNION ALL
  SELECT 4, '赤身', 'マグロ' UNION ALL
  SELECT 5, '赤身', 'カツオ' UNION ALL
  SELECT 6, '白身', 'タイ' UNION ALL
  SELECT 7, '赤身', 'サバ' UNION ALL
  SELECT 8, '白身', 'タラ' UNION ALL
  SELECT 9, '白身', 'フグ' UNION ALL
  SELECT 10, '白身', 'サケ' UNION ALL
  SELECT 11, '白身', 'サケ'
)
SELECT  * FROM testData;

1	赤身	ブリ
2	赤身	いわし
3	赤身	アジ
4	赤身	マグロ
5	赤身	カツオ
6	白身	タイ
7	赤身	サバ
8	白身	タラ
9	白身	フグ
10	白身	サケ
11	白身	サケ

SELECT  type, STRING_AGG(name)  FROM testData GROUP BY type ;

で

1	赤身	ブリ,いわし,アジ,マグロ,カツオ,サバ
2	白身	タイ,タラ,フグ,サケ,サケ

サケが重複してるのでDISTINCTつけると

SELECT  type, STRING_AGG(DISTINCT name)  FROM testData GROUP BY type ;

1	赤身	ブリ,いわし,アジ,マグロ,カツオ,サバ
2	白身	タイ,タラ,フグ,サケ

サケ消える。
DISTINCTとODER BYを同時に使うときは同じ項目でしかできない

SELECT  type, STRING_AGG(DISTINCT name ORDER BY name asc )  FROM testData GROUP BY type ;

1	赤身	いわし,アジ,カツオ,サバ,ブリ,マグロ
2	白身	サケ,タイ,タラ,フグ

区切り文字の指定はちょっと癖がある感じ

SELECT  type, STRING_AGG(DISTINCT name ,  'と'  ORDER BY name asc)  FROM testData GROUP BY type ;

1	赤身	いわしとアジとカツオとサバとブリとマグロ
2	白身	サケとタイとタラとフグ

あと今回はウィンドウ使ってないけどOVERで指定したり、LIMITも書けるので大抵のことはできそう。

Google Cloud Platform 実践ビッグデータ分析基盤開発ストーリーで学ぶGoogle BigQuery

作者:株式会社トップゲート
発売日: 2019/11/29
メディア: Kindle版

2020-12-04

令和二年度源泉徴収の給与所得控除後の給与等の計算処理実装でつらかったこと

業務で年末調整処理の一部、源泉徴収の給与所得控除後の給与等の計算を実装したけど
いろいろつらかったことをメモする。主に行政資料への不満。

従業員として出すだけでも面倒な年末調整ですが、もちろんその背後で行われている処理はもっと面倒です。

www.nta.go.jp

資料だけでこんだけ数も有り、
f:id:uyamazak:20201204174745p:plain

一括ダウンロードするとPDFで30MB超え。

でも今回、業務で必要だったのは下記の1枚だけでした。

Ⅵ　電子計算機等による年末調整
https://www.nta.go.jp/publication/pamph/gensen/nencho2020/pdf/82-83.pdf

でも、この一つだけでも結構ストレスフルでした。

コピペすると数字が全角でつらい

桁数が多いので、手打ちは避けたいのでコピペすると思うんですが、こうなります。全角です。

１，６１８，９９９円まで

電子計算機で全角数字使うのかと・・・。
コピペしたあとVSCodeの拡張で半角に変換して使いました・・・。

カラム構造がネ申エクセルのそれ

ここもコピペしたいのですが、コピペすると
f:id:uyamazak:20201204175448p:plain

こうなります。行ごとに整形しなおす必要があります。

１円から
５５１，０００　〃　
１，６１９，０００　〃　
１，６２０，０００　〃　
１，６２２，０００　〃　
１，６２４，０００　〃　
１，６２８，０００　〃　
１，８００，０００　〃　
３，６００，０００　〃　
６，６００，０００　〃　
８，５００，０００　〃　
５５０，９９９円まで
１，６１８，９９９　〃　
１，６１９，９９９　〃　
１，６２１，９９９　〃　
１，６２３，９９９　〃　
１，６２７，９９９　〃　
１，７９９，９９９　〃　
３，５９９，９９９　〃　
６，５９９，９９９　〃　
８，４９９，９９９　〃　
２０，０００，０００　〃　
０円
Ａ－５５０，０００円
Ａ×６０％＋９７，６００円
Ａ×６０％＋９８，０００円
Ａ×６０％＋９８，８００円
Ａ×６０％＋９９，６００円
Ａ×６０％＋１００，０００円
Ａ×７０％－８０，０００円
Ａ×８０％－４４０，０００円
Ａ×９０％－１，１００，００

推測ですが、エクセルでこの3カラムをつくり、行を使わずセル内部で改行してる作ってると思われます。
f:id:uyamazak:20201204175630p:plain

先日出た改善案どおりに作ってくれればこんな面倒なことしなくて済んだのに・・・。
お役所「Excel」の改善案が公開～あかんヤツ→ええヤツの例がわかりやすく、一般市民にも結構参考になる - やじうまの杜 - 窓の杜

来年この資料が直ってなかったらしかるべきところに届け出たいと思います。

計算の意図がよくわからなくて、むかつく

まあ法律でそうなってるんでしょうが、
１，６１８，９９９円から１，６２３，９９９円の狭い範囲だけ、
なんでこんな処理が必要なんでしょうか。もっとシンプルにしようって人は誰もいなかったんでしょうか。

f:id:uyamazak:20201204180059p:plain

さすがに実装して動かすと、あーこの表↓みたいな段階的な数字にしてるのかーって何となく分かったけど初見はつらい。
1000円未満切り捨てみたいのは会計でよくみるし、全部それでいいじゃんって思ってしまう・・・。
https://www.nta.go.jp/publication/pamph/gensen/nencho2020/pdf/84-92.pdf

電気計算機で実際に動くコード置いとけよ！

こんなネ申エクセルを元にしたPDF資料じゃなくて、プログラミング言語のサンプルコード置いといてくれよ！
と思ったので今回PHPで書いたコードを雑にTypeScript化、変数名に日本語を試してみたかったのもあり、Gistに置いておきました。

日本語でもVSCodeも問題なくnodeでも普通に動いた。

こういう国特有の法律用語って絶対正確な英語にはできないので、日本語のままが楽ちんだなぁと思うけど、いろいろ問題もあるので実際の現場では使えることはなさそう。

問題起きてももちろん責任は取れません。

tscして自分のPCのnode(v14.14.0)で実行するとこんなの風に出ました。

給与所得控除後の給与等の金額 { '給与': 551000, '給与所得控除後の給与等の金額': 1000 }
給与所得控除後の給与等の金額 { '給与': 1872000, '給与所得控除後の給与等の金額': 1230400 }
給与所得控除後の給与等の金額 { '給与': 2612001, '給与所得控除後の給与等の金額': 1748400 }
給与所得控除後の給与等の金額 { '給与': 4272000, '給与所得控除後の給与等の金額': 2977600 }
給与所得控除後の給与等の金額 { '給与': 9800000, '給与所得控除後の給与等の金額': 7850000 }
給与所得控除後の給与等の金額 { '給与': 20000000, '給与所得控除後の給与等の金額': 18050000 }

ちなみに来年には外部のサービスと連携してるつもりなので、こういうコードはもう二度と書かない予定です。
経理の皆様、こういうソフト作ってる皆様お疲れさまです。

プログラミングTypeScript ―スケールするJavaScriptアプリケーション開発

作者:Boris Cherny
発売日: 2020/03/16
メディア: 単行本（ソフトカバー）

Effective TypeScript: 62 Specific Ways to Improve Your TypeScript (English Edition)

作者:Vanderkam, Dan
発売日: 2019/10/17
メディア: Kindle版

2020-12-01

シニアジョブの開発メンバー、こんな人欲しいなぁ（ポエム）

これまでエンジニア職の採用面接を適当にやりすぎていて、確認し忘れが出てしまうのが良くない。
採用基準とまではいけないけど、最低限確認したいこととをまとめたい。

会社のエンジニア採用ページ。私が保守してるので内容は大丈夫なはず。
corp.senior-job.co.jp

2020/12現在のシニアジョブの状況

開発チーム体制

私を含めエンジニア職は3人、デザイナー1人、マーケ担当1人、そして社長。

開発するもの

1 .シニア人材の紹介、派遣を管理する社内システム
2. 求人サイト
3. 自社メディアサイト
4. 新規事業

1,2はコアな部分なので必須。3,4はその人の意向や得意分野による。
1,2は2016年の創業当初から残る結構つらめのコード、設計もまだまだたくさんあるので、そういうコード相手にしたことある人は安心。

確認したいこと

具体的な質問は都度考える

一人で進められる？

基本的に1人1タスクとなるので、いわゆる要件定義、設計、実装、テストから公開、保守まで全部担当してもらうことになる。
（外に出すページのデザインはデザイナーさんに頼むこともあります）

他のメンバーがサポートはもちろんするものの、利用場面や自社ビジネスもある程度理解して、既存のコードも読めないと質問もできないので、結構高めのハードルかも。

でも少なくともエンジニアチーム5人くらいまではそういう人だけにしたい。10人超えたらこれは無理そう。

LinuxとかPHPとかGitとか基礎的な教育はできない。

技術オタク？

採用ページ見てもわかる通りいろいろ使ってるし、これからも最適であればどんどん新しいの使っていく。

使ったことがない技術を見て、気が重くなるのではなく自分も使ってみたい！と思える人がいい。
使い慣れてるから、という理由だけでもっと優れてる新しいものを使わないのはイヤ。
新しい技術への興味、憧れみたいなのがある人がいい。

最近流行りの「○○に興味あります！」と言うだけでなく、実際に何か行動を起こして使ってたり、ブログとかGitHubにあげてたりすると最高。

「必要に応じて言語はなんでも使います」より、「この言語、フレームワークのこういう所が好き！」とか言える人の方がいい。

社長もコミュニケーション力はいらないと言ってた（これ言えるのすごい）。

ずっとコード書きたい人？

現状、大きなチームのマネジメント力とか、大きなプロジェクトマネジメント力とかは求めてない。
そのため将来的には自分で手を動かくのはいいやって思ってる人は現段階だと向いてなさそう。

エンジニアのためのマネジメントキャリアパス ―テックリードからCTOまでマネジメントスキル向上ガイド

作者:Camille Fournier
発売日: 2018/09/26
メディア: 単行本（ソフトカバー）

GAミント至上主義

Web Monomaniacal Developer.