GROUP_CONCAT

2022/03/12 18:25:41
お休みの日にプログラミング。


Noranekoです。



ちょっと集計システムを改修してて、


全体からあるグループで集計してその集計の中の更に細かい部分を見たい。


この場合一対多と言う構造になるので、クエリを2回もしくは


サブクエリや結合等複雑なクエリになってしまう。


例えばこんな風に

人数都道府県市区町村
1千葉県柏市
5埼玉県草加市
深谷市
三郷市
4東京都杉並区
八王子市



会員を『都道府県』別の人数と『市区町村』を同時に知りたい場合、


SELECT
COUNT(pref) '人数',
pref '都道府県',
GROUP_CONCAT(city) '市区町村'
WHERE member
GROUP BY pref;


こんな簡潔なクエリで取れてしまいます。


ちょっと感動。


ただこのままだと、


人数都道府県市区町村
1千葉県柏市
5埼玉県草加市,三郷市,深谷市,深谷市,草加市
4東京都八王子市,八王子市,杉並区,八王子市



というちょっと残念な結果になってしまいます。


しかしこれを最初の表の結果にしたい場合、


SELECT
COUNT(pref) '人数',
pref '都道府県',
GROUP_CONCAT(DISTINCT(city ORDER BY city)) '市区町村'
WHERE member
GROUP BY pref;


とこれも簡単かつ簡潔なクエリで取れます。


結果は


人数都道府県市区町村
1千葉県柏市
5埼玉県草加市,深谷市,三郷市
4東京都杉並区,八王子市



わかりやすくしかも時間もかからない。


久しぶりに便利関数に遭遇しました。


つうかMySQL4.1からあるのか…


先入観で一対多だからサブクエリだなとSQLを組んでました。


注意点としては、GROUP_CONCATの最大長が決まってるので、


戻り値が全て欲しい場合は、GROUP_CONCAT_MAX_LENを設定する必要があります。


またデリミタも第二引数で設定することで、自由に変更可能です。


使う場面は結構あると思うので、是非使ってみましょう!




気に入ったら押してね
  ブログランキング・にほんブログ村へ




~こんなアプリ出してます~

authored by Noraneko | Comment(0) | LAMP
ビックカメラ利確しなかったのが響いてて困ってます。

Noranekoです。


9/29からcertbotを使うと証明書が『ISRG Root X1』という所から発行されるみたいです。

何が問題かと言うと古いAndroid(7.1.1以下)だとエラーが出るみたいです。


正直そんな古くないじゃんと・・・

なので現在の『DST Root CA X3』を頑張ってギリギリまで使うようにしたいと思います。


# certbot certonly --manual --server https://acme-v02.api.letsencrypt.org/directory \
> --preferred-challenges dns \
> -d *.hoge.com -d hoge.com -m mail@hoge.com \
> --agree-tos --manual-public-ip-logging-ok \


以前のこれに

(詳しくはこちら

# certbot certonly --manual --server https://acme-v02.api.letsencrypt.org/directory \
> --preferred-challenges dns \
> --preferred-chain "DST Root CA X3" \
> -d *.hoge.com -d hoge.com -m mail@hoge.com \
> --agree-tos --manual-public-ip-logging-ok \


と言う感じにオプションを追加します

あれ?

あれ?

・・・



なんかうちのサイト『ISRG Root X1』になってね?

さっき見た時は『DST Root CA X3』だったんですけど・・・

2020/7/2に更新したから新しい方になるはずないんだけど・・・

よくわからね・・・

ちなみにこの新しい証明書が対応してないデバイスは以下が見れないらしいです。

https://valid-isrgrootx1.letsencrypt.org/




気に入ったら押してね
  ブログランキング・にほんブログ村へ




~こんなアプリ出してます~

authored by Noraneko | Comment(0) | LAMP

Let's Encrypt 更新作業2

2018/09/04 14:22:35
90日目前だったので更新したぜ!

Noranekoです。

今回も前回同様、手動更新しました。
その際、他のドメインの証明書も取得しようとして

-d *.studioneko.net -d studioneko.net
-d *.hokanodomain.com -d hokanodomain.com


とドメインを追加して、Expandを押して追加しました。

これをやると「studioneko.net」という名前の証明書で

「hokanodomain.com」もSSL化が出来て一元管理できるし楽だな~と思いました。

しかしこの追加したドメインを今後も使っていく予定はなく、

もしドメインを削除した時には、DNSのTXTレコード認証が通らなくなるので、

ドメインごとに証明書を取り直すことにしました。


すると、




でたああああ



studioneko.net-0001



噂には聞いてたけど、Expandを指定していてもドメインの対象が変わった為か、

見事に0001になってしまいました。

まぁでも

#certbot delete --cert-name studioneko.net-0001


で消して、もう一回同じコマンドで無事に証明書を別々に発行できました。

現在、複数のドメイン(サブドメインという意味でなく)を一つの証明書で発行してる方は、


別々に証明書を発行した方が良いですよというお話でした。

P.S.
-mのメールアドレスに関しては、ドメインごとに用意しなくても同じメールアドレスで通るので-dのところだけ変更すれば大丈夫です。




気に入ったら押してね
  ブログランキング・にほんブログ村へ




~こんなアプリ出してます~

authored by Noraneko | Comment(0) | LAMP

Let's Encrypt 更新作業

2018/06/07 14:05:56
Noranekoです。

Let's Encryptは有効期間が90日と短いので、期限前の更新が必要です。

ワイルドカードでなければ

certbot-auto renew


でcronに登録して自動更新できてたのですが、

どうやらワイルドカードではエラーが出るみたいです。

しかも新たに『/etc/letsencrypt/live/***.com-001/』という

ディレクトリが出来てしまうらしい…

Let's Encryptの証明書は、

/etc/letsencrypt/live/***.com/

でドメインごとに管理されてて、その中の証明書が最新の証明書に

シンボリックリンクされているのに、これはひどい。


どうにかして自動更新したいなと思って調べたら、

k5324さんの

Let's Encrypt の証明書をワイルドカードなやつにして自動更新できるようにした

にあるように、Cloudflareを使う必要性があります。

めんどくさいので、前回と同様に新規で取得。

DNSのテキストを更新。

その後、httpdとpostfixをrestart。

メールの証明書にも使ってる場合はpostfixを再起動するのもお忘れなく!

新規取得では『-0001』と言う謎のディレクトリが作られなかったし、

コマンドも履歴からそのまま打つだけなので

手動更新はそんなに手間じゃないですね。




気に入ったら押してね
  ブログランキング・にほんブログ村へ




~こんなアプリ出してます~

authored by Noraneko | Comment(0) | LAMP

Let's Encrypt

2018/03/16 17:53:43
いろいろと他の事をしてたので、全く更新してませんでした。

Noranekoです。



ということで、約一年半ぐらい前にLet's Encryptを使ってSSL化しました。

当時の手順を書こうと思ってて、こんなに月日が流れてしまった…

しかし!

今回新たにワイルドカードにも対応したので、ここに書き留めておきたいと思います。


先ず私はcertbotをcronしてたのでこいつを削除。

次にcertbotのディレクトリで証明書を失効します。

# ./certbot-auto revoke --cert-path /etc/letsencrypt/archive/hoge.com/cert1.pem


次のコマンドで証明書等を消します。

# ./certbot-auto delete -d hoge.com


もう使わないので、certbotディレクトリごと消します。

# rm -rf ../certbot/



今回調べてたらcertbotがyumで提供されてるので、

これは楽ちんということで、yumしちゃいます。

# yum -y install epel-release
# yum -y install certbot


そしたらcertbotを起動します。
# certbot certonly --manual --server https://acme-v02.api.letsencrypt.org/directory \
> --preferred-challenges dns \
> -d *.hoge.com -d hoge.com -m mail@hoge.com \
> --agree-tos --manual-public-ip-logging-ok \


この辺を詳しく知りたい方は、


あぱーぶろぐへ!



よくわからない人は、この方にどしどし質問しましょう!

他のサイトもちらほら技術解説があったんですが、

この方がサブドメインなしのドメインを追加していたので非常に参考になりました。

これを叩くと「DNS TXTにdeployしろよカス」

と言うのが2回表示されます。

この2回と言うのが今回重要です。

あぱーさんはすらっと流してますが、ここでほんのり躓きました。





技術ブログを書くなら、




ねこでもわかるように




親切丁寧に書いていただきたい!




なんて小心者なので言えませんが、ネット弁慶なので書き込むことはできる。

んで何故2回なのか?

ふかわりょうでもリスペクトしてるのか?

違います。

つまり*.hoge.comのみで登録するとhttps://hoge.com/でエラーが出ちゃうんです。

その為、*.hoge.comとhoge.comを登録することで2回出てくるのです。

多分。

よくわからないので、あぱーさんに聞いて。



てか意識高いQiitaとかはてなとか、ほんと使えない。

技術を参考したい時は、あぱーさんみたいなこういう個人で技術サイトを作ってる人が断然参考になる。


そんでこの値をDNSのTXTに書きます。



ここで重要なのは2個作ること!



ほんと、あぱーさんちゃんと書いておいて下さいよ!


ということで、お名前.comを使ってる方は、

ネームサーバーの設定 > DNS関連機能設定 > DNSレコード設定を利用する

の新規作成で



ホスト名に _acme-challenge

TYPEを TXT

VALUEに 表示された文字列

追加を押して、これを2個作ります。

浸透するまで5分くらい待てばいいかな?

5分待ってから何かキーを押して、



Congratulations!



が出たら成功です!

後はhttpdのconfとかに書くか、あぱーさんに聞いてSSLを設定して下さい。



PS
あぱーブログはとても参考になりました。
ありがとうございました!




気に入ったら押してね
  ブログランキング・にほんブログ村へ




~こんなアプリ出してます~

authored by Noraneko | Comment(0) | LAMP
1 2 3 
BLOG内検索
<< 2022/05 >>
Sun Mon Tue Wed Thu Fri Sat
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31