仕事と婚活と私~ITエンジニアの末路~

仕事(IT/SE)・婚活・ツイッターの話とか、いろいろ TW:@mara_ashida_

MENU

【IT】サイトのURLとタイトルを即時コピー!調査作業が少し楽ちん! ブックマークレット/Bookmarklet/JavaScript

ご無沙汰しております。。



毎日パンツをはく暇がないほど、下半身事情が多忙な 芦田マラちゃん(@mara_ashida_)です!



どう?



皆さんは、フニャフニャのチンチンとカサカサの膣に潤い与えてる?



砂漠化が止まらない?haha



さてね。

目次・流れ

No タイトル
1 概要/目的【URL管理の徹底!!!】
2-1 【非エンジニア向け】登録・利用方法
2-2 【エンジニア向け】コード
3 解説・補足

https://cdn-ak.f.st-hatena.com/images/fotolife/m/maramara_ashida/20190804/20190804024602.png


No.1概要/目的【URL管理の徹底!!!】

背景

エンジニアの方は、技術に関して調査する際、
公式ドキュメントやらBlogを漁り、メモ(Excel・txtファイル等)に残すという作業
少なからずしていると思います。


あるあるなのは「URLだけメモして、何に関するURLか失念してしまうこと」


一度、URLにアクセスして中身を見ないとダメで、


都度都度「何だっけコレ?」つって開いていたら、チリツモ(塵も積もれば・・・)で結構時間もかかるし、


フラストレーションも溜まります。


また、下記記事の通り、調査において「URL管理」を徹底することは大切です。

[★コンサルが実践するリサーチのコツ - コンサルがすなるブログといふもの]

techhack.hatenablog.jp




テーマ

というわけで、【URL+サイトのタイトル】を最低限メモするという作業を私は心がけていますが、


今回は【ブックマーク】を押下して、【URL+サイトのタイトル】を同時に一瞬で取得する方法について記載します!!! 多分、地味に便利かも。

★テーマ

ブックマークレット (Bookmarklet)で【URL+サイトのタイトル】を一瞬で取得する方法 について記録します。

https://cdn-ak.f.st-hatena.com/images/fotolife/m/maramara_ashida/20190804/20190804023740.png

<前提>
・非エンジニアでもわかる(はず)
<実行環境>
・OS:Windows10
・ブラウザ:Chrome/Edge

No.2-1【非エンジニア向け】登録・利用方法

原理はどうでもいいから、方法だけ知りたい!という人向けの手順です。

No. 内容
1 何でもいいからBookmarkに登録する
2 登録したBookmarkを編集する
3 使ってみよう!


手順1【何でもいいからBookmarkに登録する】

下記手順で、どのサイトでもいいので、BookmarkバーにBookmark登録しましょう。

https://cdn-ak.f.st-hatena.com/images/fotolife/m/maramara_ashida/20190803/20190803202922.png


手順2【登録したBookmarkを編集する】

編集します。

https://cdn-ak.f.st-hatena.com/images/fotolife/m/maramara_ashida/20190803/20190803202931.png


名前は任意です。URLに下記、プログラム(下記No.1~3のいずれか)を丸々コピーし貼付けてください。例:No.1:Prompt(入力ボックス)で表示するタイプを選択

https://cdn-ak.f.st-hatena.com/images/fotolife/m/maramara_ashida/20190803/20190803202938.png

No.1:Prompt(入力ボックス)で表示

javascript:(function(){var title = document.getElementsByTagName('title')[0].text;title = '[★'+title+']\r\n'+decodeURI(location.href)+'\r\n\r\n';title = window.prompt('下記リンクとタイトルをコピーします。',title);if(title!=null){var target = document.createElement("textarea");target.value = title;document.body.appendChild(target);target.select();document.execCommand("copy")?true:alert('コピーできませんでした。');target.parentElement.removeChild(target);}})()

No.2:Confirm(確認メッセージ)で表示

javascript:(function(){var title = document.getElementsByTagName('title')[0].text;title = '[★'+title+']\r\n'+decodeURI(location.href)+'\r\n';msg='下記リンクとタイトルをコピーします。\r\n----------------------------\r\n' + title;if(window.confirm(msg)){var target = document.createElement("textarea");target.value = title;document.body.appendChild(target);target.select();document.execCommand("copy")?true:alert('コピーできませんでした。');target.parentElement.removeChild(target);}})();

No.3:自作のMessageBoxで表示

javascript:(function(){var title = document.getElementsByTagName('title')[0].text;title = '[★'+title+']\r\n'+decodeURI(location.href)+'\r\n';var w = ( screen.width-400 ) / 2;var h = ( screen.height-150 ) / 2;var myWindow = window.open("", "myWindow", "width=400,height=150"+ ",left=" + w + ",top=" + h);myWindow.document.write("<html>");myWindow.document.write("<style type=\"text/css\">body {background:lavenderblush;}::selection {background: palegreen;}</style>");myWindow.document.write("<body>");myWindow.document.write("<p>URLとタイトルを表示します。copyしてください。</p>");myWindow.document.write("<textarea rows=5 cols=50>"+title+"</textarea>");myWindow.document.write("</body></html>");myWindow.document.getElementsByTagName('textarea')[0].select();})();
No. 内容 動作環境
1 Prompt(入力ボックス)で表示 Chrome
2 Confirm(確認メッセージ)で表示 Chrome
3 自作のMessageBoxで表示 Chrome・Edge

No.1/2はEdgeやFirefoxで、コピー機能が動作しなかったため、 やむなく、自作のMessageBoxで結果のみ表示する機能を作成しました。


手順3【使ってみよう!】

どのサイトでもいいです。表示してから、登録したBookmarkを押下します。 POPUPにて「OK」を押下すると、【URL+サイトのタイトル】をコピーできます。

例:https://qiita.com/maimax/items/c5ac541be1d475c012cf

https://cdn-ak.f.st-hatena.com/images/fotolife/m/maramara_ashida/20190804/20190804024602.png




下記のような結果を取得できます。

[★【2017年8月】先輩エンジニアに聞いた信頼できる書籍まとめ - Qiita] https://qiita.com/maimax/items/c5ac541be1d475c012cf


仕様 内容
動作環境 Chrome
実行結果 ①【★タイトル】(改行コード)URL ②Copyに失敗したときはその旨のMessageを表示
Excelに貼付けたい場合、改行コード(\r\n)ではなく、タブ記号(\t)に変えてあげるといいと思います。
注意 URLに不正な文字があると動作しないことがあります(URLのDecodeでたまにエラーになります。)。

No.1~3の結果イメージはそれぞれ下記の通りです。

https://cdn-ak.f.st-hatena.com/images/fotolife/m/maramara_ashida/20190803/20190803212037.png

No.2-2【エンジニア向け】コード

No. 内容 動作環境
1 Prompt(入力ボックス)で表示 Chrome
2 Confirm(確認メッセージ)で表示 Chrome
3 自作のMessageBoxで表示 Chrome・Edge


改行を元に戻すと下記のようなコードになります。また、コメントも追記してます。

No.1:Prompt(入力ボックス)で表示

javascript:(function(){
//リンク取得+整形
var title = document.getElementsByTagName('title')[0].text;
title = '[★'+title+']\r\n'+decodeURI(location.href)+'\r\n\r\n';

title = window.prompt('下記リンクとタイトルをコピーします。',title);

//コピー
if(title!=null){
    var target = document.createElement("textarea");
    target.value = title;
    document.body.appendChild(target);
    target.select();
    document.execCommand("copy")?true:alert('コピーできませんでした。');
    target.parentElement.removeChild(target);
}
})();

No.2:Confirm(確認メッセージ)で表示

javascript:(function(){
//リンク取得+整形
var title = document.getElementsByTagName('title')[0].text;
title = '[★'+title+']\r\n'+decodeURI(location.href)+'\r\n';

msg='下記リンクとタイトルをコピーします。\r\n----------------------------\r\n' + title;

//コピー
if(window.confirm(msg)){
    var target = document.createElement("textarea");
    target.value = title;
    document.body.appendChild(target);
    target.select();
    document.execCommand("copy")?true:alert('コピーできませんでした。');
    target.parentElement.removeChild(target);
}
})();

No.3:自作のMessageBoxで表示

javascript:(function(){
//リンク取得+整形
var title = document.getElementsByTagName('title')[0].text;
title = '[★'+title+']\r\n'+decodeURI(location.href)+'\r\n';

//自作MessageBoxの設定 ※中央寄せにするため、位置を計算
var w = ( screen.width-400 ) / 2;
var h = ( screen.height-150 ) / 2;
var myWindow = window.open("", "myWindow", "width=400,height=150"+ ",left=" + w + ",top=" + h);

//HTMLを作成
myWindow.document.write("<html>");
myWindow.document.write("<style type=\"text/css\">body {background:lavenderblush;}::selection {background: palegreen;}</style>");
myWindow.document.write("<body>");
myWindow.document.write("<p>URLとタイトルを表示します。copyしてください。</p>");
myWindow.document.write("<textarea rows=5 cols=50>"+title+"</textarea>");
myWindow.document.write("</body></html>");

//textareaを選択状態にする
myWindow.document.getElementsByTagName('textarea')[0].select();
})();

No.3 解説・補足

Bookmarkletの基本形式

Bookmarkletはウェブブラウザのブックマークなどから起動できるJavaScriptプログラムです。

javascript:(function(){
★--------好きなjavascriptのコードを書いてね--------★
})();


取得するサイト情報について
取得情報 内容
タイトル HTMLのtitleタグの情報を取得してます。
URL 日本語リンクの場合、Encodeされてしまうので、見やすいようにdecodeURIしてます。

なお、decodeURIは、不正な文字リンクの場合に失敗することがあり、回避方法がめんどくさいので、対応してません。


copy機能

No.1/2のプログラムは、Firefox(セキュリティが原因か?)やEdgeでは機能しなかったため、自作のMessageBoxで表示するタイプ(No.3)を作成しました。

copy機能は下記サイトを参考にしてます。軽く解説するとポイントは下記2点です。

Clipboard へ好きな文字列をコピーする

  • execCommand("copy")を使用するためには、textareaをbodyの下部(子要素)に作成し、select/選択する必要があります。その後、作成したtextareaを削除(removeChild)するということをしてます。
  • document.execCommand("copy")の返り値はtrue or falseです。
    「?」(条件 (三項) 演算子)で、trueの場合はtrueを返し、falseの場合は、alertを返してます。つまり、コピーに失敗した場合のみalertメッセージを表示してます。
//コピー
if(window.confirm(msg)){
    var target = document.createElement("textarea");
    target.value = title;
    document.body.appendChild(target);
    target.select();
    document.execCommand("copy")?true:alert('コピーできませんでした。');
    target.parentElement.removeChild(target);
}


bookmarkletの手軽さ

qiita.com

Qiitaで探すと、結構色んなbookmarkletが出てきます。

Webブラウザで行う定型的な作業(検索・スクレイピング・入力)があれば、活用できると思います。

(エロ動画のDownloadリンクをスクレイピングして、Download画面を表示するなんてことも・・・・)

普段意識しない当たり前の作業を自動化・効率化することも検討してはいかがでしょうか?

以上。

[★C# で COM オブジェクトを作ってみる - Qiita]
https://qiita.com/tomochan154/items/1ce33f2aef167c0fed9d#1-8

[★VBAで扱えるDLLの作成]
https://excel.syogyoumujou.com/memorandum/dll_1.html

[★C#でVBA向けの.NETライブラリ(COMコンポーネント)を作成するには? - @IT]
https://www.atmarkit.co.jp/fdotnet/dotnettips/1064combycs/combycs.html

[★忘れっぽいプログラマーの備忘録 .NetによるCOMの作成]
http://andromeda0626.blog.fc2.com/blog-entry-38.html

C:\Windows\Microsoft.NET\Framework\v4.0.30319\RegSvcs.exe /codebase C:\//////dll /tlb C:\//////////


[★VBAまたはVBSからCOM経由で使用できる.NETのライブラリの作成方法 - Qiita]
https://qiita.com/mima_ita/items/efcd1a6ea86f09047984

%BIN%\regasm  C:\dev\NMeCabCom\NMeCabCom\bin\Debug\NMeCabCom.dll /tlb /codebase


reg query HKCR\CLSID | find /i "{9F3DBFEE-FD77-4774-868B-65F75E7DB7C3}"
■DSの定義

[★【図解】コレ1枚でわかる統計学と機械学習の関係:ITソリューション塾:オルタナティブ・ブログ]
https://blogs.itmedia.co.jp/itsolutionjuku/2019/07/1_25.html
統計と機会学習

■通常の開発との違い
ドメイン知識
仮説・課題ありき
運用のしやすさ
各検定、人材像から必要なスキルを洗い出す
参考書・事例からものをつくる

■■■■■■■■■■■■■■■■■■■■■■■■■■
■有識者・ソース
■■■■■■■■■■■■■■■■■■■■■■■■■■
安宅和⼈
滋賀大学データサイエンス学部長 竹村 彰通
細谷 功
松尾豊

内閣府:AI戦略、AIガイドライン
滋賀大学データサイエンス学部
日本人工知能学会
データサイエンス協会
G検定、E検定

[★本当に知ってる!? リアルなデータ分析の世界~サイカのエンジニアが語る、話題の技術の「いま」と「未来」~]
https://www.slideshare.net/who_you_me/ss-73237524

http://www.news-postseven.com/archives/20140424_252628.html

総務省 総務省ICTスキル総合習得教材
https://www.soumu.go.jp/ict_skill/

第四次産業革命スキル習得講座認定制度

[★AI戦略 - 科学技術・イノベーション - 内閣府]
https://www8.cao.go.jp/cstp/ai/index.html


■外部の講座
インターネットを利用した大規模なオンライン講座であるMOOC(ムーク)の仕組みを利用して、「高校生のためのデータサイエンス入門」「大学生のためのデータサイエンスⅠ、Ⅱ、Ⅲ」の4つの講座

[★無料で受講できる「ディープラーニング講座」を開講 日本ディープラーニング協会:「AIとは何か」「ディープラーニングによって何ができるか」を知る - @IT]
https://www.atmarkit.co.jp/ait/articles/2104/01/news031.html

[★無料で受講できる「統計オープンデータのデータ分析入門」 総務省が開講:「社会人のためのデータサイエンス入門」と併せて学習 - @IT]
https://www.atmarkit.co.jp/ait/articles/2101/14/news023.html


■アプローチのヒント
滋賀大学のデータサイエンス学部の人材像

IPA・企業の要件を調査する

一般社団法人データサイエンティスト協会

APIエコノミー、オープンデータ活用

■
AutoML

■定義
そもそもAI>機会学習>深層学習/Deep Learning


■データ分析
予測

■
https://www.datascientist.or.jp/common/docs/skillcheck_ver3.00.pdf
データサイエンス力
データエンジニアリング
ビジネス・ドメイン知識

[★データサイエンスとデータエンジニア]
https://www.slideshare.net/nagix/ss-62777080

[★最新業界事情から見るデータサイエンティストの「実像」]
https://www.slideshare.net/takashijozaki1/cb21-seminar130830-tjo-25765789?next_slideshow=1
    

■SlideShare
[★機械学習を「社会実装」するということ / Social Implementation of Machine Learning - Speaker Deck]
https://speakerdeck.com/moepy_stats/social-implementation-of-machine-learning

[★BrainPad (@brainpadpr) on Speaker Deck]
https://speakerdeck.com/brainpadpr

■社会実装
[★機械学習や統計学を「社会実装」するということ - 渋谷駅前で働くデータサイエンティストのブログ]
https://tjo.hatenablog.com/entry/2020/07/15/173000


■SQL
[★Oracle 10gの正規表現で文字列操作を極める:SQLクリニック(1)(1/2 ページ) - @IT]
https://www.atmarkit.co.jp/ait/articles/0502/24/news131.html

[★データハンドリングのためのSQL / SQL for data handling - Speaker Deck]
https://speakerdeck.com/brainpadpr/sql-for-data-handling?slide=124



■コードの保守性
https://ytknzw.github.io/PyCon/PyCon_mini_Hiroshima/%E3%83%87%E3%83%BC%E3%82%BF%E3%82%B5%E3%82%A4%E3%82%A8%E3%83%B3%E3%82%B9%E3%81%AE%E3%81%9F%E3%82%81%E3%81%AE%E3%83%AA%E3%83%BC%E3%83%80%E3%83%96%E3%83%AB%E3%82%B3%E3%83%BC%E3%83%89%E3%81%AE%E3%82%B9%E3%82%B9%E3%83%A1_PyCon_mini_Hiroshima_20201010.pdf

http://www.scj.go.jp/ja/member/iinkai/kanji/pdf22/siryo198-5-5.pdf

Brainpad
[★機械学習]
https://ai.brainpad.co.jp/category/machine-learning/機械学習2/

[★入社1年目が教わる「はじめての人工知能」 第1回:人工知能(AI)とはなにか]
https://ai.brainpad.co.jp/1683/



[★なぜ「データ分析力」ではなく「データ活用力」が必要なのか? これから身につけるべきスキルとは:MarkeZine(マーケジン)]
https://markezine.jp/article/detail/32135


[★ITSS+(プラス)・ITスキル標準(ITSS)・情報システムユーザースキル標準(UISS)関連情報:IPA 独立行政法人 情報処理推進機構]
https://www.ipa.go.jp/jinzai/itss/itssplus.html

[★データサイエンティストの仕事の流れを知ろう|アクセンチュア]
https://www.accenture.com/jp-ja/data-scientist-training-for-women-part3


[★データの読み方 | データ分析基礎知識]
https://www.albert2005.co.jp/knowledge/statistics_analysis/statistics_basics/viewpoint

[★なぜAI時代に問題発見の重要性が増すのか?(細谷 功) | 現代新書 | 講談社(1/3)]
https://gendai.ismedia.jp/articles/-/71166

【IT】「ゴールドマンサックスの友達と銭湯行って満足度高かった!!」が本質情報なわけないだろ。 ~IT/業務系 本質情報まとめ~

導入・問題意識

https://cdn-ak.f.st-hatena.com/images/fotolife/m/maramara_ashida/20190504/20190504011555.png


こんにちは!

Twitter可愛いOLキャラでお馴染み、芦田マラちゃん(@mara_ashida_)です!

毎日、二郎インスパイアのラーメンがうめぇうめぇって言いながらインスタやってます!!

体型がデブなのは渡辺直美リスペクトとかじゃねぇから!!!

これが私らしさだから!!!

★前置きが長いのでメインに行きたい方はこちら(サイト内遷移)。

さてね。以下、真面目系のblog。

情報の量的拡大と質的充実



「情報の海」。。。。。


  • 乱立するIT系のプラットフォームやBlog
  • キュレーション・アグリゲーションされたN次情報
  • ポジショントークの行き過ぎた広告記事

これらの情報がSNSで拡散・増殖する情報社会。


「量が質を隠蔽する」と感じることの方が多いかもしれないが、
一方で、情報の量的拡大は、情報の質的充実をもたらす


情報の質を向上させるのも、情報の拡散がきっかけであり、
拡散された情報が多くの人によって議論されて、
その結果、新しい情報の生産・情報の取捨選択がさなれる。


情報の受取り方の変化

また、SNSの発達は、情報の量的拡大を加速させると同時に、我々の情報の受け取り方も変えたと思う。


具体的に言うと、Twitter等のTLでRT情報が流れてくるのを
アホ面してぼんやり待つだけになるような態度が多くなったと思う。
その結果、受動的に情報を得て、断片的な情報を鵜呑みするクセがついてしまう。


そうならないために、
「自身に必要かつ正確な情報を能動的に探し出し、考え、真贋を判別するスキルも要求されている」と感じる。


何だか、「情報の(量的)非対称性」が縮小していく社会では、「1を聞いて10を知る」よりも、
「10を聞いて1を知る」ことの方が重要ではないかとも思える。
(勿論、高度に専門化された業界・社会、ビジネスという文脈では、「1を聞いて10を知る」ような洞察力が求められると思うが。。。)

本題

前置きはさておいて、

低品質で断片的な情報が、フローとして大量に拡散される社会において、
陳腐化の波に耐えうるような、体系的な情報を理解し、ストックすることの重要性を痛感します。

https://cdn-ak.f.st-hatena.com/images/fotolife/m/maramara_ashida/20190504/20190504011555.png

というわけで

世間一般(?)で言われる「本質情報」を本Blogでは、 下記要素からなるものと定義し、
ITエンジニアとしてスキルアップにつながるような「本質情報」を蓄積・公開していきたいと思います。


  • 【空間的要素】体系性がある
    (≒断片的・分散的ではない、2つ以上の立場からの議論がある、点ではなく 2つ以上の要素が線・面・立体的な関連性を示している)
  • 【時間的要素】時間の経過とともに陳腐化されない
    (≒一過性ではない、歴史性がある、原理的である、高度に抽象化されている、時間的普遍性がある)


本Blogでは
「ゴールドマンサックスの友達が、神田にある、銭湯をべた褒めした!!!満足度たけぇ!!!」的な情報は本質情報とは言いません。

キラキラしてないし、エリートでもないBlogでございます。

(何のことか分からない人はTWにて「@oolong_ta ゴールドマンサックス」で検索してみよう!!)



改めまして、下記目的で本Blog記事を更新していきたいと思います。

<記事の目的>
・本質的な情報を備忘録として公開する。
<Target>
・エンジニア向け。自身の備忘的側面が強いですが、エンジニアに必要な知識としてご参考ください。
<特記>
・週次くらいの頻度で更新します。どんどんRichにしていきたいと思います。


(つーか、感覚的に「本質情報」って言葉が少しキモい。オタクが連呼しているのがキモいだけかもしれんが。)

※蛇足
【時間的要素】って記載しておいて定義するの難しいなと思いますね。論理を追及すると哲学的な次元になりそうな気がする。。。

ちなみに法律等の学問では、何かの概念を定義する際、
①積極的に定義する方法と②消極的に定義する方法の2つがあります。

例えば、全体集合Uがあるとき、集合Aを定義したい場合
・全体集合U = {a,b,c,d,e}
・集合A = {a,b}

①集合Aの要素 {a,b}を積極的に明文化する方法
②「Aの補集合{c,d,e} ではない」、つまり、「集合Aは{c,d,e}ではないこと」と定義する

大半が①の方法を採用していると思いますが、抽象性が高い分野では②のような【控除説】も登場します。


目次

下記の通り、①テクニカル系と②ヒューマン系を主軸にした構成にしています。
目次に対し、サイト内リンクを設定したので、ご利用ください。

No タイトル
1 【テクニカルスキル】IT系
2 【テクニカルスキル】業務系
3 【ヒューマンスキル】スキルアップ・キャリア系
4 その他・教養

https://cdn-ak.f.st-hatena.com/images/fotolife/m/maramara_ashida/20190512/20190512221618.png

Skillの俯瞰図は上記の通りです。厚労省の「ポータブルスキル活用研修」MBAのカリキュラムを元に作成しました。どこの分野に関する記事なのかを意識すると、腹落ちすると思います。

なお、昨今、AIや機械学習などが流行しており、データ分野は重点的に記事を追加していきたいと思います。


No.1 【テクニカルスキル】IT系

SQL2019/02 読了

【新社会人諸君! 仕事で扱う「データ」のことを少しだけ本気出して考えてみないか?】
  • 究極、ITは「データをどう扱う(処理する・プログラムする)か」です。データの分類を知っていると、扱うデータごとに最適な設計を検討できるようになります。
  • データには構造化データ・非構造化データがあるという話。構造化データ(2次元表)のうち、マスターデータとトランザクションデータのどちらかを意識すると、SQLの組み立て方やJOINの使い方がよく理解できると思います。

japan.zdnet.com

SQL2019/02 読了

RDBとNoSQLにみるDB近現代史 データベースに破壊的イノベーションは二度起きるか?】
  • エンジニアで、DBエンジニアのミック氏を知らない人はいないと思います。超有名SQL有識者の記事。
  • RDB/NoSQLの歴史②構造化データと非構造化データ(XML/JSON等)の違い等が読みどころ

employment.en-japan.com

SQL2019/05 読了

【今注目のデータレイクとは何か?DWHと何が違うのか?】
  • データレイクとデータウェアハウス(DWH)の違いに言及した記事。既存のDWHは構造化データ向き、データレイクは非構造化データ+構造化データ向きという整理。
  • スケーラビリティに富むPublic Cloud(例:AWSAmazon S3(Simple Storage Service) など)でデータレイクを構築する例がある模様。
    【ZOZOTOWNのDWHをRedshiftからBigQueryにお引越しした話】

blogs.oracle.com

DB2019/05 読了

【決して陳腐化しないデータベース設計の超基礎】
  • DB設計/データモデリング=物理設計・論理設計と明確に定義している点で読み手にフレンドリーな記載となっている。
  • 論理設計の要諦(①エンティティ②関連③属性④関連の多重度⑤正規化)のさわりについて言及している点で、体系性を理解しやすい

www.ulsystems.co.jp

クラウド2019/05 読了

【エンジニアが知っておくべきクラウドの7つのこと】
  • 2011年の記事で少し現状と乖離した記載があるかもしれない点には注意だが、①クラウドはクラサバ向けのシステムには不適②クラウドを利用する側のユーザー企業は、「定常的負荷はデータセンターで処理し、不足部分をクラウドでまかなう」ことが、クラウド利用の基本戦略になる等、全体最適的観点について言及している点で、気づきが多い。
  • 「所有からシェア・使用へ」という概念が波及する昨今、サブスクリプションモデルのビジネスのメリデメ・プロコンを理解することは重要だと思います。

ASCII.jp:エンジニアが知っておくべきクラウドの7つのこと

コーディング2019/04 読了

プログラマの採用面接で聞かれる、データ構造とアルゴリズムに関する50以上の質問】
  • 翻訳記事。データ構造(構造化データと非構造化データ)とアルゴリズムに関して、何ができれば必要十分かゴールのイメージを持ちやすい。
  • PGは所詮、1個1個は順次・分岐・Loopの組み合わせにすぎないですが、最適なアルゴリズムを理解すると、機能・非機能の両面に優れた実装が可能になると思います。

postd.cc 統計学データ分析2019/05 読了

【データサイエンス・スクール 統計力向上サイト】
  • 統計局のサイトで信頼度は高い。pdfのテキストがあり、初級・中級・上級に分かれている。統計・データ分析に関して体系的な知識を学べる。変なまとめサイトを見て、断片的な知識を得るよりもいいと思う。
  • サイトTOP:http://www.stat.go.jp/dss/

www.stat.go.jp

データ分析Python2019/06 読了

Pythonコードと図で分かる平均値と標準偏差の違い――「統計」とはデータから価値ある情報を抜き出すこと 】
  • 【読みどころ】数式を視覚化・Visualizeしているという点。
    ①平均値(算術平均/mean):シーソーの支点と同義。
    ②偏差:平均値(シーソーの支点)を基準0に置いた際の各データ(重り)の位置(※正負入り混じる値)
    ③分散(variance):データのバラつき具合、偏差(矢印)を2乗した値の算術平均(※2乗しているのは、②偏差の正負符号を絶対値に変換するため)
    標準偏差(Standard Deviation):分散の平方根を取った値(※③分散は、②偏差を2乗しているため、2乗を元に戻したようなイメージ)
  • 【感想】私の1個年上という。尊敬。数式・文章を直観的に理解できるように視覚化している点が秀逸。
    【TOP】「AI」エンジニアになるための「基礎数学」再入門

www.atmarkit.co.jp

AI2020/02 読了

【AIで解決すべきビジネス課題を見つけるただ1つの質問】
  • 【読みどころ】①AI(人工知能)=Machine Learning(機械学習)としたうえで、AIで解決すべきビジネス課題を見つけるために、「あなたのビジネスでは、人間が何を予測していますか?」という問いを検討するべき。AI(=機械学習)とは、予測するためのツール!②意識的に予測しているもの/無意識的に予測しているもの/予測しても意味がないもの という分類で課題設定を行うべき。
  • 【感想】「課題を解決するためにAIを使う必要はありません。使わなくても解決するならば、使わないほうがいいでしょう。トイレットペーパーを切るときにハサミを使う必要はないのです。」という記載からバランス感覚が窺われる。AIって何に向いているかということが端的にわかる良記事。

www.analyze-world.com

C#Javaコーディングアプリ2019/02 読了

【とあるコンサルタントのつぶやき .NETの例外処理 Part.1】
  • Microsoftのコンサルの方のblogです。あまり学ぶことのない例外設計に関して、公式的な考えを平たく解説しています。
  • C#Javaエンジニアは必読。try-catchの読み方がよく分かる。

blogs.msdn.microsoft.com

UI/UXデザイン2019/06 読了

【OOUX オブジェクトベースのUIモデリング
  • 【読みどころ】①オブジェクトベース(モードレス)とタスクベース(モーダル)の違いを理解する各種事例②コレクションビューとシングルビューの設計③レイアウトとインタラクション(イベント・画面遷移)など。
    「ユーザーは業務という線形の一本道ではなく、仕事空間を自由に探索し、そこにマップされた概念物たちと直接向き合うことになります。事業体において、オブジェクト(主要な概念物)は変化しにくく、タスク(業務)は変化しやすいのです。だからタスクベースのUI構成は複雑化しやすく、すぐに破綻してしまいます。」
  • 【感想】エンジニア必読!感覚的・経験的に行いがちなUI設計を科学的手法で行う。再現性・汎用性のある記載。大変示唆に富む記事だと思います。

www.sociomedia.co.jp

UI/UX2019/03 読了

【UI/UXの観点から見た業務系ツールを開発するときに気をつけたい20項目】
  • 体系性には欠けますが、自身なりの画面設計方針を構築する上での材料になります。
  • 「10.マニュアルは読まないと思ってよい」は、対応策が参考になります。

mae.chab.in

UI/UXデザイン2019/05 読了

【UIデザインにおけるアイコンの重要性】
  • UIにおけるアイコンの役割=「ユーザーに表現したい対象の概念を直感的に理解させる記号であると同時に、サービスやコンテンツ独自のコンテキストを付け加えたもの」
  • 普段意識しないアイコンに関する、気付きの多い記事。ちなみに、私は普段、ツール開発やPPT資料の作成でも意識して取り入れてます。

goodpatch.com

IT2020/03 読了

【リコーの全力投球RPA、数万人の従業員を動かした7つの起爆剤
  • 【読みどころ】
    ①社内広報活動を展開して従業員が積極的に参加したくなるようなムード作り(社内報にマンガ形式で業務プロセス改革やRPAについて分かりやすく解説するコンテンツ)
    ②経営トップ自らが活動の意義や重要性について発信する
    ③組織横断型の推進部門CoE(Center of Excellence)の設置+現場部門にもRPAの推進チームと「キーパーソン」を任命
    ④クリエーターの資格試験で開発資格を付与・ガバナンス
    ⑤管理職に向けて業務プロセス改革に関する教育や啓発
    ⑥社内RPA相談窓口(開かれた情報基盤とコミュニティー)の設置
    ⑦社内イベント、資格認定制度でモチベUP
  • 【感想】RPAの啓発活動や、開発・運用の体制作り、ガバナンスの維持、教育体系の構築、情報を共有できる場の設置、現場のモチベーション維持のための工夫。RPAに限らず、新しい仕組みを組織的に導入し、定着させるための本質的なプロセスが読み取れる。

www.itmedia.co.jp


No.2 【テクニカルスキル】業務系

会計2019/05 読了

【会計業務の基本を理解する】
  • 金融系エンジニア必読かと。B/S(ストック)とP/L(フロー)の違いなどが滅茶苦茶腹落ちする。簿記のテキスト読む前に絶対読みたい。
  • 証券の現場でP/Lの計算をするEUCツールは山のようにあります。本業務知識を理解すれば、顧客業務の本質的理解に繋がるかと。

tech.nikkeibp.co.jp 法律開発2019/05 読了

アジャイル開発を円滑に行うための契約モデルを考える--アジャイル開発を実案件に生かすための基礎知識】
  • 就活生や新人SEは契約関連のことに疎いと思います。通常のウォーターフォール開発の契約形態(請負契約)とアジャイル開発の契約形態(準委任契約)に関して比較されていてわかりやすいので必読。
  • 現実問題として「ウォーターフォールはクソ、アジャイル最高」とか言っているのは、メリデメを比較した上での発言ではないと思うので要注意です。

japan.zdnet.com 業務効率化2019/06 読了

【生産性が低い業務はアウトソースすべき】

blog.livedoor.jp

仕事論業務効率化2019/06 読了

【コンサルが実践するリサーチのコツ】
  • 【読みどころ】①リサーチ設計(計画)を行う(例:上司とレビューポイントの合意形成を行う)②詳しそうな人に情報乞食する③画像検索④Platformとなるサイトを事前に把握しておく。⑤mind「リサーチからどんな示唆を出したいのか、明確にしろ」
  • 【感想】mind系の内容と実践的な方法論に関する内容が整理されていて体系性・再現性があると感じました。また、調査って時間があっても足りない。時は金なり。「詳しそうな人に情報乞食する」が効果的というのも改めて理解しました。

techhack.hatenablog.jp


No.3 【ヒューマンスキル】スキルアップ・キャリア系

キャリア2019/05 読了

【就活生へ、世の中には5パターンの仕事しかない。早めに全部経験しておこう】
  • 大胆に仕事を5パターンに分類しています。自身の仕事が本質的にどこに相当するか意識すると、進路選択に有用と思います。
  • ①0から1を作る仕事②1から9まで育てていく仕事③9まで出来たものを10に仕上げる仕事④数字が下がらないように踏ん張る仕事⑤マイナスを0にする仕事

newswitch.jp

キャリア2019/06 読了

Googleエバンジェリストをやめてメドレーに入社した僕が6つに割れたバキバキの腹筋を手に入れるまでに実行した7つのステップ】
  • 【読みどころ】新しい市場で戦っていくためには、一般的に言われる「仕事力や優秀さ」といった要素をある程度前提としながらも、それ以上に「多様な経験や個性」という要素の方が大事なんじゃないかな、と感じます。
  • 【感想】タイトルはネタですが笑。熱意と論理のバランスを感じます。プレゼンのプロの方の文章なので、すんなりと読めます。

www.wantedly.com

キャリア2019/05 読了

【「やりたくない仕事」が教えてくれた大事なこと】
  • 『若い頃の「やりたい」にあまり意味はない』とあるように、若手のころのキャリア選択は「WANT」に拘泥しない方がよい。NEED⇒CAN⇒WANTの優先順位で進路選択すべきと説いています。
  • 『やりたいこと至上主義」に捕らわれる必要もありません。最短距離で行くより、寄り道をしたことが、自分の人生に良い影響を与えることもあります。』

baigie.me

スキルビジネス2019/06 読了

【「連想力」クリエイティブな発想力を高める思考法】
  • 【読みどころ】「連想」とは1つの物事から何かしら関係性のある別の物事を発想すること。連想法を3パターンに分類し、事例とともに効用を紹介している。①同じような業態特性を持った事象間での「業態連想」、②同じ目的を持った事象間での「目的連想」、③同じような環境に身を置いている事象間での「環境連想」。
  • 【感想】「連想スキル」は俗に言う「創造性」と私自身理解しており、それは天性の才能ではなく、再現性のある科学的アプローチで向上させることができるという点が示唆に富んでいると思う。また、私は下記の通り、スティーブ・ジョブズのスピーチを連想しました。
    創造性は知識と知識を結びつける力によって生み出される

web.archive.org

人材キャリア2019/06 読了

【Π型人材とは】
  • 【読みどころ】文部科学省の記載の引用をしており権威付けをしかっりしている記事。
    企業はもはや単独領域だけでは競争に勝つことが難しくなっているため。事業・企業・商品としての独自性を発揮したり、課題を複数の視点から解決したりするには、Π型人材が不可欠である。
  • 【感想】人材論においては「○○人間が社会では求められる!」と様々な方が(手を変え品を変え)持論を述べてらっしゃりますが、本質的にはテクニカルスキル(専門性)とヒューマンスキルの2要素の組み合わせに過ぎないと思います。覚えやすいイメージしやすいという意味で、「パイ型」と命名するのはいいと思います。また、自身の強みは何か分析し、キャリア設計にも資する記事です。

studyhacker.net

スキル2019/06 読了

キーエンス「一生食える」営業力の秘訣 】
  • 【読みどころ】①購買意思決定者≒キーパーソンを見分け適切な提案を迅速に行う②セールスパーソンの【コンピテンシー/営業プロセス/思考の型】の標準化
  • 【感想】プロダクトの品質が高いことは前提だと思いますが、セールスのスキルを属人化させず標準化させることが肝かと。特に、思考の型が同じだとコミュニケーションも円滑に行われそう。

www.nikkei.com

スキル2020/02 読了

【脅しも謝罪も必要ナシ。スゴ腕の債権回収OLに教わった、「人の動かし方」がスゴい】
  • 【読みどころ】①お願いではなく、「質問」する ②相手に寄り添い、罪悪感を突く ③ネガティブな表現をひとつ書いたら、ポジティブな表現を2つ書くという“比率”を意識
  • 【感想】人を動かす感情にFOG(霧)(:恐怖心(Fear)、義務感(Obligation)、罪悪感(Guilt))があるというのも学びになった。

ten-navi.com


No.4 その他・教養

思考2019/06 読了

【エッセンス(本質)とフォーム(形態)、2つの次元を往復せよ】
  • 【読みどころ】豊富な引用から博識ぶりが窺えます。[1]抽象化→[2]概念化→[3]具体化の流れを形式/形態と実質/本質という2軸で説明している点。
    「良い彫刻家が人間の胴体を作る時、彼の再現するのは筋肉ばかりではありません。其は筋肉を活動させる生命です」
    「われわれが輪郭線を写し出す時は、内に包まれている精神的内容で其を豊富にするのです」「美は性格の中にあるのです。情熱の中にあるのです。美は性格があるからこそ、若しくは情熱が裏から見えて来るからこそ存在するのです。肉体は情熱が姿を宿す型(ムーラージ)です」──高村光太郎ロダンの言葉』より
  • 【感想】村山昇氏の記事は現代文でも取り上げられるほど著名な方です。どの記事も面白いし、高校生の頃に読んでおけばよかったと思うものばかりですね。

globis.jp

UI/UX2018/12 読了

【デザインとアートの違いについてちゃんと考えてみたからシンプルに解説する】
  • 「アートは表現と創造・問いかけ、デザインは問題解決と価値創造」ということが端的にわかる。下記リンクなどもオススメ。
  • 【デザインの力で行動が変わる!人々を動かす粋なアイデア9選】 https://creive.me/archives/5645/

https://zakkan-vivi.com/difference-between-art-and-design/zakkan-vivi.com ※↑NotFoundと表示されることがありますが、「zakkan-vivi.com」をクリックすると遷移します。

美術/ART2019/05 読了

【Artpedia 近現代美術の百科事典】
  • 美術的概念・美術様式を体系的に学べる。全てを読み切るのは大変ですが、ここで得た知識があると美術館とかもより楽しめると思います。

www.artpedia.jp

スキル2019/06 読了

【スキルの習得に10,000時間は必要ない!たった20時間でOKな理由は】
  • 【読みどころ】適切な方法を実践すると「「まあまあ良い」レベルまでなら20時間で到達できる」。「1日約45分を約1ヶ月間続ければOKなレベル」。
    方法:①スキルを分解すること②自己修正ができるくらいまで学習すること③練習の妨げとなるものを排除すること④最低でも20時間は練習すること
  • 【感想】motivation UP!!! 20hガンバロウ!!

164s.net

文章力2019/02 読了

【文章表現の幅を広げる「レトリック講座」】
  • 比喩の種類に関して、分かりやすくまとまっています。Twitter力・文章力向上につながるかも。

https://kakuyomu.jp/works/1177354054883758297/episodes/1177354054883758306kakuyomu.jp

数学2019/05 読了

【4次元講座】
  • 4次元とは何か、次元の考え方がよくわかる。導入のドラえもんの記事が面白い。

www32.atwiki.jp

教養2020/01 読了

【超入門! お城セミナー】
  • 【読みどころ】お城の分類・構造物・歴史・鑑賞方法等について平易に記載している。
  • 【感想】国内旅行に行ったとき城って、まぁ惰性で巡りますけど、城の楽しみ方がわかるコンテンツになっている。知識を得ることで世界が広がるってこういうことだと思います。旅行前に一読したい!

shirobito.jp

教養2019/07 読了

京都大学学術情報リポジトリ (KURENAI)】
  • 【読みどころ】オープンアクセスを推進するプラットフォームとして、京都大学で日々創造される研究・教育成果(学術雑誌掲載論文、学位論文、紀要論文など)をインターネット上で公開
  • 【感想】800 京都大学刊行資料・900 京都大学シンポジウム・公開講座等など、時間があれば読み進めたいです。無料で読めるってすごい。

repository.kulib.kyoto-u.ac.jp


★随時更新していくので、bookmark登録等、よければ!また、これおすすめだよ!という記事があれば教えてください笑

【IT】SQL/同一グループの中で最大/最小のレコードと集計結果を同時に取得する #SQL/GROUP BY/CASE式

導入

業務アプリ開発でDB操作・SQLは必須ですね。体系性に欠けますが個人的な知見を記したいと思います。
下記エントリの応用編となります。

mara-ashida.hatenablog.com

https://cdn-ak.f.st-hatena.com/images/fotolife/m/maramara_ashida/20190427/20190427165449.png

★テーマ

ざっくり言うと、各グループにおいて、点数などがTOPやWORSTであるようなレコードを「集計結果と併せて」表示する方法について記載します。

正確に言うと、GROUP BYで集計した際、「集計結果と併せて」各グループでAカラムの値が最大/最小となるようなBカラムの値を抽出する方法 について記録します。

例を挙げると、GROUP BYで、場所ごとにお店の件数を集計した際、集計結果と併せて、レビュー点数が最大のお店も表示する方法です。

GROUP BY句で指定したカラムにおいて、値が同じ行は1行に集約されてしまい、
「各行の値を参照して、特定の条件を満たすレコードを抽出する」という発想が難しいので記載しようと思いました。

目次・流れ

No タイトル
1 概要/事例/目的
2 コード/実行結果
3 解説・補足
<前提>
・データ操作言語(※DML/Data Manipulation Language)について概要を理解していること。
※SELECT/INSERT/UPDATE/DELETEなどテーブルに対するデータの取得・追加・更新・削除を行うコマンド。
・事例等は極力シンプルな記載にしています。

<実行環境>
DBのミドルウェアPostgreSQL 10.6, compiled by Visual C++ build 1800, 64-bit
※SELECT version();で取得
Oracle/SQLServerでも有効な記事です。

概要/事例/目的

食べログデータを使用して検証します。

場所ごとに下記①②を調査するイメージです。
(例:銀座とかで1番点数の高いお店を、集計結果と併せて取得する。)

  • ①集計結果:店舗数(COUNT)/最大の点数(MAX)など
  • ②場所ごとの、点数が最も高いお店の名前

集合論的なイメージは下記の通り。
各グループに集約操作を行いつつ、各グループで点数が最大(or最小)のデータを取得します。
https://cdn-ak.f.st-hatena.com/images/fotolife/m/maramara_ashida/20190427/20190427165449.png

コード/実行結果

テーブル定義

食べログのお店データを登録するテーブルです。

アトリビュート カラム名 PK(主キー) NOT NULL制約
お店ID SHOP_ID integer -
店名 SHOP_NAME varchar 100 -
場所 PLACE varchar 50 -
種類 KIND varchar 100 - -
点数 REVIEW_SCORE numeric - - -
レビュー数 REVIEW_CNT integer - - -
CREATE TABLE t_tabelog_shop_list( 
  shop_id INTEGER NOT NULL
  , shop_name VARCHAR (100) NOT NULL
  , place VARCHAR (50) NOT NULL
  , kind VARCHAR (100)
  , review_score NUMERIC
  , review_cnt INTEGER
  , PRIMARY KEY (shop_id)
) ;

データサンプル

件数が多いので、本ページの一番下に全量を記載します。こちら(サイト内遷移)。

INSERT INTO t_tabelog_shop_list VALUES ( 1, '支那麺 はしご 本店', '銀座駅', '担々麺、ラーメン', 3.59, 857 );

(省略)

INSERT INTO t_tabelog_shop_list VALUES ( 80, 'シンガポール・シーフード・リパブリック 銀座', '銀座一丁目駅', 'シンガポール料理、バイキング、アジア・エスニック料理(その他)', 3.57, 239 );

https://cdn-ak.f.st-hatena.com/images/fotolife/m/maramara_ashida/20190429/20190429004325.png

コード

SELECT
  place 場所
  , COUNT(*) 店舗数
  , MAX(review_score) 最大点数
  , MAX(review_cnt) 最大口コミ数

/*****★POINT★******/
  , MAX( 
    CASE WHEN
    NOT EXISTS (
      SELECT 1 FROM t_tabelog_shop_list B
      WHERE A.place = B.place AND A.review_score < B.review_score) 
    THEN A.shop_name ||'/'|| A.kind ||'/' || A.review_score
  ELSE NULL END
  ) 対象地域で一番点数が高い店
/*****★POINT★******/

FROM
  t_tabelog_shop_list A
GROUP BY place ORDER BY place;

※一見すると分かりづらいですが、単純化すると下記の通り。 SELECT句のMAX関数の引数にCASE式が入っているだけです。
※CASE式の条件●●●にはNOT EXISTS(相関サブクエリ)が記載されています。

SELECT
,(省略)

/*****★POINT★******/

,MAX(CASE WHEN ●●● ELSE NULL END)
--※●●●には【NOT EXISTS(相関サブクエリ)】

/*****★POINT★******/
FROM
  t_tabelog_shop_list A
GROUP BY place ORDER BY place;


実行結果

https://cdn-ak.f.st-hatena.com/images/fotolife/m/maramara_ashida/20190427/20190427165443.png

解説

<操作>

基礎知識

・(説明不要だと思いますが)場所ごとに店舗数などを集計しています。

・CASE式の返り値の「||」演算子
文字列の結合を行います。店名に併せてジャンルと点数も結合して表示しています。

・NOT EXISTS演算子
副問い合せの結果が存在しないとき真になります。

【今回のPOINT概要】
  • ①MAX/MN関数は文字列を返すことができる!
  • ②MAX/MN関数の中でCASE式を使用する!
  • ③CASE式の条件にNOT EXISTS(相関サブクエリ)を利用する!

コードの概要を再掲します。

SELECT
,(省略)

/*****★POINT★******/

,MAX(CASE WHEN ●●● ELSE NULL END)
--※●●●には【NOT EXISTS(相関サブクエリ)】

/*****★POINT★******/
FROM
  t_tabelog_shop_list A
GROUP BY place ORDER BY place;


★POINT①MAX/MN関数は文字列を返すことができる!

・集計関数の返り値には2種類あります。

  • ①SUM/AVG/COUNTなど:数値を返すのが原則
  • ②MAX/MIN:数値・文字列を返す

⇒今回、特定の店名(文字列)を知りたいのでMAX/MINを使用します。

★POINT②MAX/MN関数の中でCASE式を使用する!

・対象の場所/placeの集合に対し、レコードを1件ずつCASE文で走査してます。具体的には

  • ①NOT EXISTS⇒条件を満たすコードが存在しない場合、店名などを返す
  • ②①以外の場合、NULLを返す(Dead Logic/到達不能コードですが)
--(★コード再掲★)
SELECT
  place 場所
  , COUNT(*) 店舗数
  , MAX(review_score) 最大点数
  , MAX(review_cnt) 最大口コミ数

/*****★POINT★******/
  , MAX( 
    CASE WHEN
    NOT EXISTS (
      SELECT 1 FROM t_tabelog_shop_list B
      WHERE A.place = B.place AND A.review_score < B.review_score) 
    THEN A.shop_name ||'/'|| A.kind ||'/' || A.review_score
  ELSE NULL END
  ) 対象地域で一番点数が高い店
/*****★POINT★******/

FROM
  t_tabelog_shop_list A
GROUP BY place ORDER BY place;


★POINT③CASE式の条件にNOT EXISTS(相関サブクエリ)を利用する!

今回実現したいことは①「場所毎に」②「点数が最大のお店」を求めることです。NOT EXISTSの中身で【相関サブクエリ】を利用し、上記条件を実装します。

  • ①「場所毎に」:
    【WHERE A.place = B.place】 という条件がこれに相当します。
  • ②「点数が最大のお店」:
    「点数が最大」=「該当レコード以外に、より大きい点数の店がない」と考えます。
    ⇒【NOT EXISTS(SELECT ・・・ WHERE ・・・ A.review_score < B.review_score)】 という条件がこれに相当します。

※【相関サブクエリ】のイメージ
・外側のテーブル(別名:A)のレコードが1行ずつ、内側のテーブル(別名:B)と比較されるようなイメージです。
・(正確ではないかもしれませんが)個人的なイメージとして、 外側のテーブル(別名:A)のレコードが1行ずつ、内側のテーブル(別名:B)に代入されるようなイメージを持っています。

補足

・点数が最大のお店が2店舗以上あった場合、MAX関数を利用しているため「文字列として最大値」のレコードが選択されます。

例:有楽町駅は最大点数が3.58の店が2店舗ある
・MAX関数を利用した場合(文字列として最大値):「小洞天 有楽町店」 が返る
・MIN関数を利用した場合(文字列として最小値):「シェイクシャック 東京国際フォーラム」 が返る

・通常、最大点数の一覧をサブクエリに書いて、 INNER JOINで店名を抽出するといった操作をすると思いますが、 当BlogのようにCASE式1つで記載することが可能です。

参考サイト

【同一グループの中で最大のレコードを取得する SQL を書く】
http://labs.timedia.co.jp/2014/10/selecting-max-record-in-group-by.html
【同一グループの中で最大のレコードを取得する SQL を書く】
https://note.miyabis.jp/2009/02/26327410.html
【第9回 SQLでループ! 相関サブクエリの使い方~切れ過ぎるナイフにご用心~ (2)相関サブクエリ】
http://gihyo.jp/dev/serial/01/sql_academy2/000902

万一、誤った理解をしている場合はTwitterまたは本Blogにてご指摘いただけると幸いです。 以上。

Sample Data

INSERT INTO t_tabelog_shop_list VALUES ( 1, '支那麺 はしご 本店', '銀座駅', '担々麺、ラーメン', 3.59, 857 );
INSERT INTO t_tabelog_shop_list VALUES ( 2, 'グルガオン', '銀座一丁目駅', 'インド料理、インドカレー', 3.64, 760 );
INSERT INTO t_tabelog_shop_list VALUES ( 3, '銀座 久兵衛 銀座本店', '新橋駅', '寿司', 3.79, 718 );
INSERT INTO t_tabelog_shop_list VALUES ( 4, 'ラデュレ サロン・ド・テ 銀座三越店', '銀座駅', 'マカロン、カフェ、フレンチ', 3.60, 605 );
INSERT INTO t_tabelog_shop_list VALUES ( 5, '煉瓦亭', '銀座駅', '洋食、オムライス、その他肉料理', 3.15, 576 );
INSERT INTO t_tabelog_shop_list VALUES ( 6, 'アーンドラ・ダイニング 銀座', '銀座一丁目駅', 'インド料理、インドカレー、ダイニングバー', 3.75, 517 );
INSERT INTO t_tabelog_shop_list VALUES ( 7, 'ロオジエ', '銀座駅', 'フレンチ', 4.41, 512 );
INSERT INTO t_tabelog_shop_list VALUES ( 8, '大衆割烹 三州屋 銀座店 ', '銀座一丁目駅', '定食・食堂、居酒屋', 3.59, 489 );
INSERT INTO t_tabelog_shop_list VALUES ( 9, 'カーン・ケバブビリヤニ', '新橋駅', 'インド料理、インドカレーパキスタン料理', 3.61, 457 );
INSERT INTO t_tabelog_shop_list VALUES ( 10, 'びすとろ UOKIN', '新橋駅', 'ビストロ、居酒屋、バル・バール', 3.54, 444 );
INSERT INTO t_tabelog_shop_list VALUES ( 11, 'デリー 銀座店', '銀座駅', 'インド料理、インドカレー、居酒屋', 3.60, 441 );
INSERT INTO t_tabelog_shop_list VALUES ( 12, 'RIGOLETTO KITCHEN', '内幸町駅', 'イタリアン、スペイン料理、パスタ', 3.53, 440 );
INSERT INTO t_tabelog_shop_list VALUES ( 13, 'シクスバイオリエンタルホテル', '有楽町駅', 'イタリアン、カフェ、バー', 3.58, 435 );
INSERT INTO t_tabelog_shop_list VALUES ( 14, 'アピシウス', '日比谷駅', 'フレンチ', 4.34, 431 );
INSERT INTO t_tabelog_shop_list VALUES ( 15, '銀座うかい亭', '東銀座駅', '鉄板焼き、ステーキ', 4.23, 430 );
INSERT INTO t_tabelog_shop_list VALUES ( 16, 'ラ・メール・プラール', '有楽町駅', 'ビストロ、西洋各国料理(その他)、カフェ', 3.53, 420 );
INSERT INTO t_tabelog_shop_list VALUES ( 17, 'てんぷら 近藤', '銀座駅', '天ぷら', 4.02, 417 );
INSERT INTO t_tabelog_shop_list VALUES ( 18, '銀座千疋屋 銀座本店 フルーツパーラー', '銀座駅', 'フルーツパーラー、パフェ、ケーキ', 3.59, 392 );
INSERT INTO t_tabelog_shop_list VALUES ( 19, '銀座 しまだ', '新橋駅', '割烹・小料理、魚介料理・海鮮料理、居酒屋', 3.92, 371 );
INSERT INTO t_tabelog_shop_list VALUES ( 20, 'インペリアルバイキング サール', '日比谷駅', 'バイキング、洋食・欧風料理(その他)、西洋各国料理(その他)', 3.63, 367 );
INSERT INTO t_tabelog_shop_list VALUES ( 21, 'ダルマサーガラ', '東銀座駅', 'インドカレー、アジア・エスニック料理(その他)、野菜料理', 3.65, 361 );
INSERT INTO t_tabelog_shop_list VALUES ( 22, 'Peter', '日比谷駅', 'ステーキ、洋食、バー', 3.59, 359 );
INSERT INTO t_tabelog_shop_list VALUES ( 23, 'ぴょんぴょん舎 GINZA UNA', '銀座駅', '焼肉、冷麺、韓国料理', 3.58, 354 );
INSERT INTO t_tabelog_shop_list VALUES ( 24, '魚金 本店', '新橋駅', '魚介料理・海鮮料理、居酒屋、日本酒バー', 3.55, 351 );
INSERT INTO t_tabelog_shop_list VALUES ( 25, '俺の割烹 銀座本店', '新橋駅', '割烹・小料理、日本酒バー、居酒屋', 3.60, 350 );
INSERT INTO t_tabelog_shop_list VALUES ( 26, 'エスキス', '銀座駅', 'フレンチ、モダンフレンチ', 4.57, 348 );
INSERT INTO t_tabelog_shop_list VALUES ( 27, '焼肉の名門 天壇 銀座店', '東銀座駅', '焼肉', 3.59, 345 );
INSERT INTO t_tabelog_shop_list VALUES ( 28, 'サーラ アマービレ', '銀座一丁目駅', 'イタリアン', 3.94, 343 );
INSERT INTO t_tabelog_shop_list VALUES ( 29, 'パラダイス ダイナシティ', '銀座駅', '中華料理、飲茶・点心、シンガポール料理', 3.58, 342 );
INSERT INTO t_tabelog_shop_list VALUES ( 30, 'パークサイドダイナー', '日比谷駅', '洋食、パンケーキ、ハンバーガー', 3.60, 338 );
INSERT INTO t_tabelog_shop_list VALUES ( 31, 'うち山', '銀座一丁目駅', '懐石・会席料理', 3.94, 337 );
INSERT INTO t_tabelog_shop_list VALUES ( 32, 'ベージュ アラン・デュカス 東京', '銀座一丁目駅', 'フレンチ', 3.73, 332 );
INSERT INTO t_tabelog_shop_list VALUES ( 33, '銀座 シェ・トモ', '銀座一丁目駅', 'フレンチ', 3.63, 330 );
INSERT INTO t_tabelog_shop_list VALUES ( 34, '資生堂パーラー 銀座本店', '新橋駅', '洋食、ハヤシライス、オムライス', 3.67, 328 );
INSERT INTO t_tabelog_shop_list VALUES ( 35, '筑紫樓 銀座店', '銀座駅', '中華料理', 3.78, 327 );
INSERT INTO t_tabelog_shop_list VALUES ( 36, 'ビヤホールライオン 銀座七丁目店', '銀座駅', 'ビアホール・ビアレストラン、ビアバー、ステーキ', 3.58, 327 );
INSERT INTO t_tabelog_shop_list VALUES ( 37, 'RESTAURANT DAZZLE', '銀座一丁目駅', 'イタリアン、ワインバー、バー', 3.59, 326 );
INSERT INTO t_tabelog_shop_list VALUES ( 38, 'ビーフン東', '新橋駅', '台湾料理', 3.58, 317 );
INSERT INTO t_tabelog_shop_list VALUES ( 39, 'ブラッスリーオザミ丸の内', '有楽町駅', 'フレンチ、ビストロ、ワインバー', 3.58, 311 );
INSERT INTO t_tabelog_shop_list VALUES ( 40, 'ブラッスリー ポール・ボキューズ 銀座', '銀座一丁目駅', 'ビストロ、フレンチ', 3.58, 310 );
INSERT INTO t_tabelog_shop_list VALUES ( 41, '梅丘寿司の美登利総本店 銀座店', '内幸町駅', '寿司', 3.57, 309 );
INSERT INTO t_tabelog_shop_list VALUES ( 42, '竹葉亭 銀座店', '銀座駅', 'うなぎ、割烹・小料理、懐石・会席料理', 3.60, 307 );
INSERT INTO t_tabelog_shop_list VALUES ( 43, 'ジョーズ シャンハイ ニューヨーク 銀座店', '銀座一丁目駅', '中華料理、飲茶・点心、広東料理', 3.58, 293 );
INSERT INTO t_tabelog_shop_list VALUES ( 44, '銀座 鮨 かねさか 本店', '新橋駅', '寿司', 4.08, 292 );
INSERT INTO t_tabelog_shop_list VALUES ( 45, '俺のフレンチ TOKYO', '銀座一丁目駅', 'ビストロ、フレンチ、バル・バール', 3.58, 289 );
INSERT INTO t_tabelog_shop_list VALUES ( 46, 'シェイクシャック 東京国際フォーラム', '有楽町駅', 'ハンバーガー、カフェ、ダイニングバー', 3.58, 288 );
INSERT INTO t_tabelog_shop_list VALUES ( 47, '新ばし しみづ', '新橋駅', '寿司', 4.13, 285 );
INSERT INTO t_tabelog_shop_list VALUES ( 48, 'エール', '銀座駅', 'イノベーティブ・フュージョン、フレンチ、モダンフレンチ', 4.17, 282 );
INSERT INTO t_tabelog_shop_list VALUES ( 49, '銀之塔', '東銀座駅', 'シチュー', 3.59, 282 );
INSERT INTO t_tabelog_shop_list VALUES ( 50, 'フィッシュバンク トーキョー', '新橋駅', 'フレンチ、イタリアン、ステーキ', 3.59, 282 );
INSERT INTO t_tabelog_shop_list VALUES ( 51, 'バー&ラウンジ トゥエンティエイト コンラッド東京', '汐留駅', 'バー、ラウンジ、カフェ', 3.30, 276 );
INSERT INTO t_tabelog_shop_list VALUES ( 52, '焼肉 うしごろ 銀座店', '銀座一丁目駅', '焼肉、ホルモン', 4.15, 273 );
INSERT INTO t_tabelog_shop_list VALUES ( 53, '一味玲玲 新橋本店', '新橋駅', '中華料理、餃子', 3.59, 274 );
INSERT INTO t_tabelog_shop_list VALUES ( 54, 'ザ・ロビー', '日比谷駅', '洋食・欧風料理(その他)、カフェ、ラウンジ', 3.59, 272 );
INSERT INTO t_tabelog_shop_list VALUES ( 55, 'イタリア料理 フィオレンツァ', '銀座一丁目駅', 'イタリアン、ステーキ、パスタ', 3.59, 272 );
INSERT INTO t_tabelog_shop_list VALUES ( 56, '俺のやきとり 銀座9丁目', '新橋駅', '焼鳥', 3.58, 268 );
INSERT INTO t_tabelog_shop_list VALUES ( 57, 'イタリアンバル UOKIN 新橋店', '新橋駅', 'イタリアン、バル・バール、ワインバー', 3.53, 268 );
INSERT INTO t_tabelog_shop_list VALUES ( 58, '舞浜', '新橋駅', '居酒屋、割烹・小料理、魚介料理・海鮮料理', 3.60, 265 );
INSERT INTO t_tabelog_shop_list VALUES ( 59, '佐賀牛 季楽 銀座 ', '銀座駅', '鉄板焼き、ステーキ', 4.06, 263 );
INSERT INTO t_tabelog_shop_list VALUES ( 60, '遊食豚彩 いちにいさん 日比谷店', '日比谷駅', '豚しゃぶ、とんかつ、かき氷', 3.58, 261 );
INSERT INTO t_tabelog_shop_list VALUES ( 61, 'コカレストラン&マンゴツリーカフェ 有楽町', '有楽町駅', 'タイ料理、タイスキ、カフェ', 3.51, 259 );
INSERT INTO t_tabelog_shop_list VALUES ( 62, 'パリのワイン食堂', '東銀座駅', 'バル・バール、ステーキ、フレンチ', 3.58, 258 );
INSERT INTO t_tabelog_shop_list VALUES ( 63, '岡半 本店', '銀座駅', '鉄板焼き、すき焼き、ステーキ', 3.58, 257 );
INSERT INTO t_tabelog_shop_list VALUES ( 64, 'ヘイフンテラス', '日比谷駅', '広東料理、中華料理、飲茶・点心', 3.61, 255 );
INSERT INTO t_tabelog_shop_list VALUES ( 65, '小洞天 有楽町店', '有楽町駅', '中華料理、担々麺、飲茶・点心', 3.58, 254 );
INSERT INTO t_tabelog_shop_list VALUES ( 66, '銀座 吉宗', '新橋駅', '和食(その他)、ちゃんぽん、郷土料理(その他)', 3.58, 254 );
INSERT INTO t_tabelog_shop_list VALUES ( 67, 'ジリオ', '汐留駅', 'イタリアン', 3.69, 251 );
INSERT INTO t_tabelog_shop_list VALUES ( 68, '蕎麦 流石', '東銀座駅', 'そば、懐石・会席料理', 3.63, 250 );
INSERT INTO t_tabelog_shop_list VALUES ( 69, '末げん', '新橋駅', '鳥料理、懐石・会席料理、親子丼', 3.58, 249 );
INSERT INTO t_tabelog_shop_list VALUES ( 70, 'インペリアルラウンジ アクア', '日比谷駅', 'ラウンジ、バー、カフェ・喫茶(その他)', 3.54, 249 );
INSERT INTO t_tabelog_shop_list VALUES ( 71, '桃花源', '新橋駅', '四川料理、中華料理、スイーツ(その他)', 3.62, 247 );
INSERT INTO t_tabelog_shop_list VALUES ( 72, '俺のイタリアンJAZZ', '新橋駅', 'イタリアン、バル・バール、ピザ', 3.58, 247 );
INSERT INTO t_tabelog_shop_list VALUES ( 73, '銀座 天ぷら 阿部', '銀座駅', '天ぷら', 3.58, 247 );
INSERT INTO t_tabelog_shop_list VALUES ( 74, 'ジムトンプソンズテーブル タイランド 銀座', '銀座一丁目駅', 'タイ料理、ダイニングバー、バイキング', 3.58, 246 );
INSERT INTO t_tabelog_shop_list VALUES ( 75, '銀座 天國 本店', '新橋駅', '天ぷら、天丼・天重、懐石・会席料理', 3.57, 246 );
INSERT INTO t_tabelog_shop_list VALUES ( 76, 'アロッサ 銀座店', '銀座一丁目駅', 'ビストロ、バル・バール、オセアニア料理', 3.58, 242 );
INSERT INTO t_tabelog_shop_list VALUES ( 77, '焼肉 銀座コバウ', '銀座駅', '焼肉、韓国料理、牛料理', 4.12, 241 );
INSERT INTO t_tabelog_shop_list VALUES ( 78, 'レストラン タテル ヨシノ 銀座', '東銀座駅', 'フレンチ、モダンフレンチ', 4.04, 241 );
INSERT INTO t_tabelog_shop_list VALUES ( 79, 'ヌガ', '東銀座駅', 'ビストロ、ワインバー、フレンチ', 3.60, 241 );
INSERT INTO t_tabelog_shop_list VALUES ( 80, 'シンガポール・シーフード・リパブリック 銀座', '銀座一丁目駅', 'シンガポール料理、バイキング、アジア・エスニック料理(その他)', 3.57, 239 );

【IT】JavaScriptで食べログの店舗情報を取得する方法/Webスクレイピング/csvデータ #食べログ #JavaScript

導入

blogさぼってましたが、IT関連は自身の備忘のために記録したいと思います。

★テーマ

JavaScript食べログの店舗情報をWebスクレイピングする方法について記録します。

HTMLのxpathの勉強のためにやってみました。

食べログAPIサービスは停止しているとのことで、店舗情報を取得するには下記のように地道にWebスクレイピングするしかないようです(?)(2019/04現在)。

https://cdn-ak.f.st-hatena.com/images/fotolife/m/maramara_ashida/20190414/20190414022211.png

目次・流れ

No タイトル
1 概要/事例/目的
2 コード/実行結果
3 解説・補足
4 ソース改良版(2019/04/20)
<前提>
JavaScript/CSS/HTMLの違いを理解していること
JavaScriptの基本文法について理解していること
JavaScriptは完全独学のため、コーディング規約等遵守していないです。
<実行環境>
・OS:Windows10
・ブラウザ:Chrome バージョン: 73.0.3683.103(Official Build) (64 ビット)

概要/事例/目的

テストで利用するDBにサンプルデータを登録しようと思ったのですが
実データっぽいものがないかな?と思い、食べログのデータを抽出してみました。

下記の赤枠(※)の箇所に関して、カンマ区切りのデータを抽出するscriptを記載しました。
※店名,場所,ジャンル,点数,口コミ数(銀座のお店を検索しました。)

https://cdn-ak.f.st-hatena.com/images/fotolife/m/maramara_ashida/20190414/20190414013508.png

銀座・新橋・有楽町のグルメ情報 口コミ数順 [食ベログ]

コード/実行結果

実行画面

実行方法は下記の通り。gifをご参考ください。

  • ①対象食べログ画面を表示
    (※【任意】口コミ数順にしてます)
  • Chrome>F12キー>Consoleタブ にて下記コードを張り付けて、Enterキーで実行

https://cdn-ak.f.st-hatena.com/images/fotolife/m/maramara_ashida/20190414/20190414013601.gif

コード

スマホだと意味不明だと思うので、PCでご覧ください。

/* 初期化*/
var work = '';
var result
= 'No,店名,場所,ジャンル,点数,口コミ数\r\n'; 
/*出力header情報*/

/*●変数
・shop_name:店名(class名で取得)
・shop_summry:概要(class名で取得)
・shop_rate:点数(class名で取得 ※点数が表示されない場合がある。要素の個数に応じて処理を分岐。)
・shop_reviews:口コミ数(class名で取得)

※class名やHTMLの構造は食べログサービスによって変更されるものであり、動作を常に保証するものではありません。
*/
var shop_name 
= document.getElementsByClassName("list-rst__rst-name-target cpy-rst-name");
var shop_summry 
= document.getElementsByClassName("list-rst__area-genre cpy-area-genre");
var shop_rate 
= document.getElementsByClassName("list-rst__rate");
var shop_reviews 
= document.getElementsByClassName("list-rst__rvw-count-num cpy-review-count");

for(var i = 0; i < shop_name.length; ++i){
    
  /*点数エリアの要素数*/
  var val_area_cnt 
  = document.getElementsByClassName("list-rst__rate")[i].children.length;

  /*点数が表示される場合*/
  if ( val_area_cnt == 2){
    work = (i+1) 
    +','+ shop_name[i].innerText 
    +','+ ((shop_summry[i].innerText).replace(' / ',',')).trim()
    +','+ shop_rate[i].children[0].innerText 
    +','+ shop_reviews[i].innerText 
    + '\r\n';
result = result + work;
  /*点数が表示されない場合*/
  } else if (val_area_cnt == 1){
    work = (i+1) 
    +','+ shop_name[i].innerText 
    +','+ ((shop_summry[i].innerText).replace(' / ',',')).trim()
    +','+ 'No Score' 
    +','+ shop_reviews[i].innerText 
    +  '\r\n';
    result = result + work;
  }
};

実行結果

食べログは1ページごとに20店舗表示されるので、2ページ以降もページ遷移して、地道に取得してみてください。

No,店名,場所,ジャンル,点数,口コミ数
1,支那麺 はしご 本店,銀座駅 251m,担々麺、ラーメン,3.59,857
2,グルガオン,銀座一丁目駅 33m,インド料理、インドカレー,3.64,760
3,銀座 久兵衛 銀座本店,新橋駅 357m,寿司,3.79,718

(~省略~)

18,銀座千疋屋 銀座本店 フルーツパーラー,銀座駅 25m,フルーツパーラー、パフェ、ケーキ,3.59,392
19,銀座 しまだ,新橋駅 375m,割烹・小料理、魚介料理・海鮮料理、居酒屋,3.92,371
20,インペリアルバイキング サール,日比谷駅 300m,バイキング、洋食・欧風料理(その他)、西洋各国料理(その他),3.63,367

下記のように、textデータを整形してエクセルに表示してみると、結構壮観です。

https://cdn-ak.f.st-hatena.com/images/fotolife/m/maramara_ashida/20190414/20190414013511.png

解説

<操作>

  • getElementsByClassName("Class名")メソッド
  • 返り値:HTMLCollection ※配列(Array)ではないです。

上記メソッドでHTMLCollection[0]~[19](合計20店舗分)を取得できます。
本HTMLCollectionをfor文でLoop処理して、innerTextを取得しているだけです。

<補足>

  • 【場所とジャンル】は' / '(スラッシュ)で1つのデータとして結合されている(例:「銀座駅 251m / 担々麺、ラーメン」)ので、 カンマ区切りに置換してます。また、前後の半角スペースを削除してデータクレンジングしてます。

  • 食べログに点数が表示されない場合があるため、「No Score」と表示するようにしてます。 異常値データが混在しており、どこまでプログラムで場合分けするか悩ましいですね。

  • 大前提ですが、class名やHTMLの構造は食べログサービスによって変更されるものであり、本scriptの動作を常に保証するものではありません。 ご注意ください。

以上。

【婚活】非モテ・エンジニア流!食べログでデートに使えるお店を知る3つのコツ - 仕事と婚活と私~ITエンジニアの末路~

ソース改良版(2019/04/20)

■修正内容
・夜の価格帯・昼の価格帯の追加
・分岐のコードの共通化

/* 初期化*/
var work = '';
var result
= 'No,店名,場所,距離,ジャンル,点数,口コミ数,夜価格(From),夜価格(To),昼価格(From),昼価格(To)\r\n'; 
/*出力header情報*/

/*●変数 下記class名で取得
・shop_name:店名
・shop_summry:概要
・shop_rate:点数
・shop_reviews:口コミ数
・shop_budget_dinner:夜の価格帯
・shop_budget_lunch:昼の価格帯
※class名やHTMLの構造は食べログサービスによって変更されるものであり、動作を常に保証するものではありません。
*/
var shop_name 
= document.getElementsByClassName("list-rst__rst-name-target cpy-rst-name");
var shop_summry 
= document.getElementsByClassName("list-rst__area-genre cpy-area-genre");
var shop_rate 
= document.getElementsByClassName("list-rst__rate");
var shop_reviews
= document.getElementsByClassName("list-rst__rvw-count-num cpy-review-count");
var shop_budget_dinner
= document.getElementsByClassName("c-rating__val list-rst__budget-val cpy-dinner-budget-val");
var shop_budget_lunch
= document.getElementsByClassName("c-rating__val list-rst__budget-val cpy-lunch-budget-val");

for(var i = 0; i < shop_name.length; ++i){
    
  /*点数エリアの要素数*/
  var val_area_cnt 
  = document.getElementsByClassName("list-rst__rate")[i].children.length;
  
  work = (i+1) 
  +','+ shop_name[i].innerText 
  +','+ shop_summry[i].innerText.replace(' / ',',').trim().replace(' ',',');
  /*点数が表示される場合*/
  if ( val_area_cnt == 2){
    work = work 
    +','+ shop_rate[i].children[0].innerText;
  /*点数が表示されない場合*/
  } else if (val_area_cnt == 1){
    work = work 
    +','+ 'No Score' ;
  }
  work = work 
  +','+ shop_reviews[i].innerText.replace(' - ','0')
  +','+ shop_budget_dinner[i].innerText.replace(/¥/g,'').replace(/,/g,'').replace('~',',')
  +','+ shop_budget_lunch[i].innerText.replace(/¥/g,'').replace(/,/g,'').replace('~',',')
  + '\r\n';
  result = result + work;
};

某4人兄弟バンドに関して宣伝させてください。

2019年内に私の好きなバンド【SaToMansion】が解散の危機を迎えています!

マジで勘弁して欲しいけど。彼らも本気ということです。

彼らの曲を聴いて下さい。マジで掛値なしにかっこいい。

そしてYou Tubeのチャンネル登録をお願いします!

www.youtube.com www.youtube.com www.youtube.com

【IT】SQL/GROUP BY で集約操作したグループに特定のフラグを立てる ~COUNT(CASE)~ #SQL

導入

blogさぼってましたが、IT関連は自身の備忘のために記録したいと思います。

★テーマ

GROUP BY で集約操作したグループに特定のフラグを立てる方法について記録します。
より一般化した表現をすると、集合の性質を調べる方法とでもいいましょうか。

個人で実装していたとき、ふと思いついたんですが、Webを見ると既出のようですね。

https://cdn-ak.f.st-hatena.com/images/fotolife/m/maramara_ashida/20190410/20190410002132.png


目次・流れ

No タイトル
1 概要/事例/目的
2 コード/実行結果
3 解説・補足
<前提>
・データ操作言語(※DML/Data Manipulation Language)について概要を理解していること。
※SELECT/INSERT/UPDATE/DELETEなどテーブルに対するデータの取得・追加・更新・削除を行うコマンド。
・事例等は極力シンプルな記載にしています。

<実行環境>
DBのミドルウェアPostgreSQL 10.6, compiled by Visual C++ build 1800, 64-bit
※SELECT version();で取得
Oracle/SQLServerでも有効な記事です。

概要/事例/目的

社内で利用するアンケートアプリを作成した際、アンケート毎に下記を表示したいとのことになりました。

  • ①全体の回答数
  • ②自分が回答済みか否かのフラグ【0:未回答/1:回答済】をステータスとして表示したい。

本アンケートには、証券アナリストの方がヒアリングをかけたい質問などを設定します。

「アンケート毎に」とあるのでGROUP BY句の出番というのは容易に想像がつきます。

集合論的なイメージは下記の通り。
各グループに集約操作を行いつつ、各グループの性質・フラグを設定します。

https://cdn-ak.f.st-hatena.com/images/fotolife/m/maramara_ashida/20190410/20190410002132.png

コード/実行結果

テーブル定義

アンケート結果を記録するテーブルです。

CREATE TABLE t_analyst_answer( 
  QUESTION_ID CHAR (8) NOT NULL
  , REG_USER varchar (50) NOT NULL
  , ANSWER CHAR (2) NOT NULL
  , REG_DATE CHAR (8)
  , REG_TIME CHAR (4)
  , PRIMARY KEY (QUESTION_ID, REG_USER)
) ;

データサンプル

INSERTINTO t_analyst_answerVALUES ('Q0000001', '芦田マラ', 'C1', '20190408', '1200');
INSERTINTO t_analyst_answerVALUES ('Q0000001', 'TOM', 'C2', '20190408', '1200');
INSERTINTO t_analyst_answerVALUES ('Q0000001', 'ANDY', 'C1', '20190408', '1200');
INSERTINTO t_analyst_answerVALUES ('Q0000001', 'MIKE', 'C3', '20190408', '1200');
INSERTINTO t_analyst_answerVALUES ('Q0000001', 'BILL', 'C3', '20190408', '1200');
INSERTINTO t_analyst_answerVALUES ('Q0000002', 'TOM', 'C2', '20190408', '1200');
INSERTINTO t_analyst_answerVALUES ('Q0000002', 'ANDY', 'C3', '20190408', '1200');
INSERTINTO t_analyst_answerVALUES ('Q0000002', 'MIKE', 'C2', '20190408', '1200');
INSERTINTO t_analyst_answerVALUES ('Q0000002', 'BILL', 'C2', '20190408', '1200');
INSERTINTO t_analyst_answerVALUES ('Q0000003', '芦田マラ', 'C1', '20190408', '1200');
INSERTINTO t_analyst_answerVALUES ('Q0000003', 'MIKE', 'C3', '20190408', '1200');
INSERTINTO t_analyst_answerVALUES ('Q0000003', 'BILL', 'C3', '20190408', '1200');

https://cdn-ak.f.st-hatena.com/images/fotolife/m/maramara_ashida/20190410/20190410002128.png
上記のように、質問ID(QUESTION_ID)と登録ユーザ(REG_USER)をキーに、アンケート結果を記録するテーブルを設計しました。
回答(ANSWER)には選択肢(choice)のコードを適当に設定しています。

コード

SELECT
QUESTION_ID AS 質問ID
, COUNT(*) AS 件数

, COUNT(
    CASE WHEN REG_USER = '芦田マラ' THEN 1 ELSE NULL END
    ) 回答済FLG

FROM
  t_analyst_answer 
GROUP BY
  QUESTION_ID 
ORDER BY
  QUESTION_ID; 

実行結果

https://cdn-ak.f.st-hatena.com/images/fotolife/m/maramara_ashida/20190410/20190410002124.png

解説

<操作>

・質問IDごとに件数を集計しています。
・対象の質問IDの集合に対し、レコードを1件ずつCASE文で走査してます。具体的には

  • ①REG_USER = '芦田マラ'がTRUEのとき、COUNT関数でTRUEの件数が集計されます。
  • ②COUNT(NULL)の際、0を返すという性質を利用してます。

・'芦田マラ'を社員IDなどにすれば、自分が回答済みかどうかということがわかります。

/*再掲*/
SELECT
QUESTION_ID AS 質問ID
, COUNT(*) AS 件数
/*●OK*/
, COUNT(
    CASE WHEN REG_USER = '芦田マラ' THEN 1 ELSE NULL END
    ) 回答済FLG
/*COUNTという集約関数の引数に対し、REG_USERを指定*/

/*●NG*/
/*,CASE WHEN REG_USER = '芦田マラ' THEN 1 ELSE 0 END
GROUP BY句に存在しない列名をSELECT句に指定するとエラーになるよ!
*/

FROM
  t_analyst_answer 
GROUP BY
  QUESTION_ID 
ORDER BY
  QUESTION_ID; 

<補足>

GROUP BY句に存在しない列名をSELECT句に指定するとエラーになりますが、
集約関数の引数に対しては、GROUP BYにない列名を設定できます。
そのため、COUNTという集約関数の引数に対し、REG_USERを指定しています。

SQLのGROUP BY句の処理を理解する - ぱと隊長日誌

以上。

某4人兄弟バンドに関して宣伝させてください。

2019年内に私の好きなバンド【SaToMansion】が解散の危機を迎えています!

マジで勘弁して欲しいけど。彼らも本気ということです。

彼らの曲を聴いて下さい。マジで掛値なしにかっこいい。

そしてYou Tubeのチャンネル登録をお願いします!

www.youtube.com

www.youtube.com

www.youtube.com