What's New Qiita

20190506のSQLに関する記事は4件です。

  • 投稿日：2019-05-06T22:09:51+09:00

自分用勉強メモ9日目

tips

502 Bad Gateway

HTTPのエラーコードの1つ．
400番台がクライアント側のエラー（File not found とか）であるのに対し，500番台はサーバ側のエラーを示す．
今回は，ゲートウェイやプロキシが不正なリクエストを受け取ったことを示している．

参考：HTTP502（Bad Gateway）の原因と対処法

SQLインジェクションの1つの解

管理者でのログインを試みる際，ユーザ名がadminで決め打ちだった場合，

admin'--

と入力すればパスワードが何であれログインに成功する．

調べた知識

Base64

英数字と「+」「-」「=」のみを使って，マルチバイト文字等をエンコードする方式．これによって，英数字+@しか使えない通信環境でもマルチバイト文字等を扱うことができる．
エンコード・デコードには以下のサイトが便利である．

オンラインBase64でデコーダ

ヴィジュネル暗号

換字式暗号の1つ．
アルファベットの対応表をもとに平文を暗号化する．
1つ1つ目で追って解読しても解けるが，スクリプトを書いて楽にしたい．

vigenere_cipher.py
import sys


class MyVigenere:
    def encode(text, key):
        ret = ''
        for i in range(len(text)):
            offset = (ord(text[i]) + ord(key[i]) - 2 * ord('a')) % 26
            ret += chr(ord('a') + offset)
        return ret

    def decode(text, key):
        ret = ''
        for i in range(len(text)):
            offset = (ord(text[i]) - ord(key[i])) % 26
            ret += chr(ord('a') + offset)
        return ret


if __name__ == '__main__':
    plaintext = sys.argv[1]
    key = sys.argv[2]
    flag = MyVigenere.decode(plaintext, key)
    print(flag)

John The Ripper

パスワードクラッキングツール．
以下，Ubuntuでの実行例を示す．

// ツールのインストール
# apt install john
// パスワードファイルとシャドウファイルを結合する
$ unshadow passwd shadow > tmp
// johnコマンドに渡す
$ john --show tmp

robots.txt

検索エンジンのクローラーのアクセスを制限するためのファイル．
見せたくないページはここに記述するらしい．

picoCTF

きなこ(@kinako_software)さんのブログ(こちら)を拝見して，cpawCTFの次はこれをやるといいよと書いてあったので取り組んでみる．
ヒントが書かれているので，もしわからなかったとしても調べればなんとかなりそう．

Logon

ユーザ名とパスワードを入力してログインする．
何を入力してもログインに成功するが，adminでログインしたい．
Cookieを見ると，ログイン成功後にadminという変数がFalseで格納されている事がわかる．
これをTrueに書き換えてリロードするとログイン成功．

Recovering From the Snap

ディスクイメージファイルが渡される．
fileで見てみると，FATであることがわかる．
Ubuntuで，以下のコマンドを実行してマウントする．

# mount -t vfat animals.dd /mnt/animal/

これで/mnt/animal/以下に4つの画像が現れた．
Linuxでは，jpgファイルはeogコマンドで開くことができる．
しかし，どの画像を見てもフラグに関係するようには思えない．
ヒントを見てみると，いくつかのファイルが削除されてしまっているらしい．
FTK Imagerというツールを使うと，ディスクイメージをマウントしたときに削除済みのファイルにもアクセスできるらしい．
MacやLinuxではコマンドラインツールしか利用できず使い方が難しかったので，Windows版をダウンロードした．
与えられたイメージを開くと，フラグが書かれている画像が削除されていることがわかる．
そのファイルを開けばフラグを獲得できる．

admin panel

与えられたpcapファイルをWiresharkで開く．
/loginにPOSTしている部分があるので，そのパケットを見ると，パスワードのところにフラグが書かれている．

hertz

換字暗号だったので，便利そうなツールを調べたところ，quipqiupというのを見つけた．
「Puzzle:」の部分に暗号化されたテキストを貼り付けて「Solve」を押すだけ．

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-05-06T12:50:19+09:00

N+1の予感がしたらincludesを追加?

こんにちは！
入社したてのころ、右も左もわからずにコーディングをしていました。
そんな中で、僕もよく悩まされたN+1についての対策について簡単にまとめてみました。
N+1は簡単に防げてパフォーマンスをあげることができます。
すぐできて、効果大なのでぜひ実践してみてください！

そもそもN+1とは

SQLクエリが 「データ量N + 1回 」走ってしまい、取得するデータが多くなるにつれて（Nの回数が増えるにつれて）パフォーマンスを低下させてしまう問題です。

N+1問題 / Eager Loading とは
(引用させていただきました)

→簡単にいうとデータ取得の際、余計にSQLを発行してしまいパフォーマンスを下げてしまうことです。

テーブルの定義

例えば配列展開でBook(本)からAuthor(著者)の名前を出力したい場合(以下ER図&作成データになります)。
スクリーンショット 2019-05-05 20.13.02.png

念の為コードも

Author

class Author < ApplicationRecord
  has_many :books
end

Book

class Book < ApplicationRecord
  belongs_to :author
end

データの用意

author(諫山さん)が6冊の本(book)のリレーションを持っています。

irb(main):004:0> Author.all # 全てのAuthorレコード
  Author Load (0.5ms)  SELECT "authors".* FROM "authors"
+----+--------+-------------------------+-------------------------+
| id | name   | created_at              | updated_at              |
+----+--------+-------------------------+-------------------------+
| 1  | 諫山創 | 2019-05-05 10:44:51 UTC | 2019-05-05 10:44:51 UTC |
+----+--------+-------------------------+-------------------------+

irb(main):003:0> Book.all # 全てのBookレコード
  Book Load (1.3ms)  SELECT "books".* FROM "books"
+----+---------+-----------+-------------------------+-------------------------+
| id | title   | author_id | created_at              | updated_at              |
+----+---------+-----------+-------------------------+-------------------------+
| 1  | 進撃の1 | 1         | 2019-05-05 10:46:17 UTC | 2019-05-05 10:46:17 UTC |
| 2  | 進撃の2 | 1         | 2019-05-05 10:46:25 UTC | 2019-05-05 10:46:25 UTC |
| 3  | 進撃の3 | 1         | 2019-05-05 10:46:28 UTC | 2019-05-05 10:46:28 UTC |
| 4  | 進撃の4 | 1         | 2019-05-05 10:46:31 UTC | 2019-05-05 10:46:31 UTC |
| 5  | 進撃の5 | 1         | 2019-05-05 10:46:35 UTC | 2019-05-05 10:46:35 UTC |
| 6  | 進撃の6 | 1         | 2019-05-05 10:46:38 UTC | 2019-05-05 10:46:38 UTC |
+----+---------+-----------+-------------------------+-------------------------+

irb(main):004:0> Author.first.books # 諫山さんが6冊の本(book)のリレーションを保持
  Author Load (1.7ms)  SELECT  "authors".* FROM "authors" ORDER BY "authors"."id" ASC LIMIT ?  [["LIMIT", 1]]
  Book Load (0.2ms)  SELECT "books".* FROM "books" WHERE "books"."author_id" = ?  [["author_id", 1]]
+----+---------+-----------+-------------------------+-------------------------+
| id | title   | author_id | created_at              | updated_at              |
+----+---------+-----------+-------------------------+-------------------------+
| 1  | 進撃の1 | 1         | 2019-05-05 10:46:17 UTC | 2019-05-05 10:46:17 UTC |
| 2  | 進撃の2 | 1         | 2019-05-05 10:46:25 UTC | 2019-05-05 10:46:25 UTC |
| 3  | 進撃の3 | 1         | 2019-05-05 10:46:28 UTC | 2019-05-05 10:46:28 UTC |
| 4  | 進撃の4 | 1         | 2019-05-05 10:46:31 UTC | 2019-05-05 10:46:31 UTC |
| 5  | 進撃の5 | 1         | 2019-05-05 10:46:35 UTC | 2019-05-05 10:46:35 UTC |
| 6  | 進撃の6 | 1         | 2019-05-05 10:46:38 UTC | 2019-05-05 10:46:38 UTC |
+----+---------+-----------+-------------------------+-------------------------+

コンソールで実行

配列展開でBookから親モデルのAuthorのnameを呼び出すとBookのデータ数分(6個)SQLを発行してしまいます。
→つまり5回もSQLが無駄に発行されてしまうのです。

books = Book.all
> Book Load (0.2ms)  SELECT "books".* FROM "books"

irb(main):037:0* books.each do |book|
irb(main):038:1*  book.author.name
irb(main):039:1> end
  Author Load (0.6ms)  SELECT  "authors".* FROM "authors" WHERE "authors"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
  Author Load (0.1ms)  SELECT  "authors".* FROM "authors" WHERE "authors"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
  Author Load (0.1ms)  SELECT  "authors".* FROM "authors" WHERE "authors"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
  Author Load (0.1ms)  SELECT  "authors".* FROM "authors" WHERE "authors"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
  Author Load (0.1ms)  SELECT  "authors".* FROM "authors" WHERE "authors"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
  Author Load (0.1ms)  SELECT  "authors".* FROM "authors" WHERE "authors"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
irb(main):040:1>

スクリーンショット 2019-05-05 20.25.32.png

この無駄なクエリを防ぐにはincludesを追加するのがもっとも簡単です

includes追加

  books = Book.all.includes(:author) #追加
  Book Load (2.1ms)  SELECT "books".* FROM "books"
  Author Load (0.2ms)  SELECT "authors".* FROM "authors" WHERE "authors"."id" = ?  [["id", 1]]

お気づきでしょうか、includes追加により関連レコード(author)も一緒に取得されています。
→つまり、Bookと一緒に紐づいたAuthorモデルのレコードも取得し変数に代入していることになります。

Before

books = Book.all
> Book Load (0.2ms)  SELECT "books".* FROM "books"

books = Bookモデルの全てのデータ

After
スクリーンショット 2019-05-05 20.33.31.png
books = Bookモデルの全てのデータとそれらにひもづくAuthorデータ

この状態でもう一度booksを展開してみましょう！

irb(main):051:0* books.each do |book|
irb(main):052:1*   book.author.name
irb(main):053:1> end
irb(main):054:0>

今度は展開のたびにAuthorを取得していません。
SQLの発行をおさえてパフォーマンス低下を防ぐことができましたね。

includesで色々なリレーションを取得する

先ほどはN:1(Book:Author)でのパターンでしたが、実際はもっと複雑な利用パターンが多いと思います。
そんな時に利用できる書き方をご紹介します。

N:1 = Book:Author

  books = Book.all.includes(:author)

こちらは先ほどのパターンでしたね

N:1:1 = Book:Author:Profile

では、Authorのプロフィール情報を保存するAuthors::Profileがあった場合

  books = Book.all.includes(author: :profile)
   Book Load (1.6ms)  SELECT  "books".* FROM "books" LIMIT ?  [["LIMIT", 11]]
  Author Load (0.4ms)  SELECT "authors".* FROM "authors" WHERE "authors"."id" = ?  [["id", 1]]
  Authors::Profile Load (0.4ms)  SELECT "authors_profiles".* FROM "authors_profiles" WHERE "authors_profiles"."author_id" = ?  [["author_id", 1]]

N:1:1:1 = Book : Author : Profile : ProfileImage

さらにProfileに1つのプロフィール写真Authors::ProfileImageひもづく場合

  books = Book.all.includes(author: [profile: :profile_image])
　 Book Load (0.5ms)  SELECT  "books".* FROM "books" LIMIT ?  [["LIMIT", 11]]
  Author Load (0.1ms)  SELECT "authors".* FROM "authors" WHERE "authors"."id" = ?  [["id", 1]]
  Authors::Profile Load (0.2ms)  SELECT "authors_profiles".* FROM "authors_profiles" WHERE "authors_profiles"."author_id" = ?  [["author_id", 1]]
  Authors::ProfileImage Load (0.1ms)  SELECT "authors_profile_images".* FROM "authors_profile_images" WHERE "authors_profile_images"."id" = ?  [["id", nil]]

逆に1:N(Author:Book)なら?
これはリレーションを使い関連データを全て取得できますね。

  > Author.first.books
  Author Load (0.3ms)  SELECT  "authors".* FROM "authors" ORDER BY "authors"."id" ASC LIMIT ?  [["LIMIT", 1]]
  Book Load (0.3ms)  SELECT "books".* FROM "books" WHERE "books"."author_id" = ?  [["author_id", 1]]

N+1の予感とタイトルにありますが、余計にクエリを投げるケースは配列展開が多いと思います。
慣れていない方はeachやmap, selectなど配列展開のメソッドを使う際にN+1が起きていないかぜひ意識してみてください！

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-05-06T01:19:17+09:00

【Big�Query】 商品データを分析・可視化

12d696d6167652d73746f72652e73332e616d617a6f6e6177732e636f6d2f302f3132303839382f30313366663136332d333730642d373335382d376363612d38393466373932.png

概要

ECサイトの商品データ約30,000件をBigQueryで集計、Googleデータポータルで可視化・分析し、データの特徴や傾向把握を試みます

対象データ

今回対象とするのは、「Amazonにおけるインナーウェア製品データ」です

i01.png

スクリーンショット 2019-05-05 18.01.04.png

上記のように、「商品名／価格（ドル）／ブランド名／レビュー件数..」等のデータが約30,000件入っています
Kaggleにて公開されているので、CSVファイルとしてダウンロードしてきます

※実際のデータでは実在のブランド名が使われていますが、この記事内では全て仮名に置き換えています

▼ BigQueryで集計

データの用意

まずはBigQueryにデータを入れていきます

データセット作成

「データセットを作成」クリック

i02.png

データセットIDを入力

i03.png

i04.png

プロジェクトに、空のデータセットが作成されました

テーブル作成

「テーブルを作成」をクリック。データソースとしてCSVファイルをアップロード

i05.png

i06.png

CSVファイルが読み込まれ、テーブルが作成されました

SQL

現時点でデータは約30,000行

ただ、数が多いのは分析対象であるインナーウェアの「色ごと／サイズごと」で行が分けられているためで、Amazonでは色／サイズが違っても「1商品扱い（ひとまとめにレビューされる）」で設定されている場合が多くあります

今回のインナーウェアも、商品によっては30バリエーション以上ある商品もありますが、それも「1商品扱い」となります

今回「1商品扱い」となる条件は以下と設定します

  • ブランド名が同一
  • 商品名が同一
  • レビュー：評価が同一
  • レビュー：件数が同一

以上を踏まえ、SQLで商品情報の重複（同じ商品のバリエーション違い）をグルーピングします

SELECT
  brand_name,
  product_name,
  rating,
  review_count,
  TRUNC (AVG (price)) price_avg
FROM
  `practice-01-234805.amazon_products.az_innerwear`
GROUP BY
  brand_name,
  product_name,
  rating,
  review_count
ORDER BY
  brand_name,
  product_name,
  review_count
  DESC
;

「1商品扱い」でも、価格が違う場合があるので（とてもややこしいですが…）、価格に関してはグルーピングした「平均」を着地とし、TRUNC関数で小数点以下を切り捨てています

i07.png

「色／サイズ違い」をひとまとめにした結果、約30,000行あったデータが約400行にまとまり、各商品の平均価格が算出できました

▼ Googleデータポータルで可視化

BigQueryで集計したデータを、Googleデータポータルで可視化し、分析していきます
「データポータルで調べる」をクリック

i08.png

Googleデータポータルにデータが引き継がれます

i09.png

グラフ選択

分析に適したグラフを選択

i10.png

ディメンション／指標設定

分析したいディメンションと指標を設定

i11.png

可視化 結果

今回は各ブランドごとのレビュー数の比較や、価格帯ごとの商品数やレビュー数の比較等を行います

ブランド割合／レビュー投稿数の割合

egt57rst8.png

左の円グラフ「ブランド割合」で見ると、商品全体の半数以上を「wc」というブランドが占めており、次に「ck」というブランドが続きます

さらに、右の円グラフ「レビュー投稿数の割合」では「wc」が7割以上を占めており、このブランドはレビューの獲得にとくに成功していると言えます

ブランド毎の平均価格とレビュー評価

スクリーンショット 2019-05-03 18.39.18.png

上記は、ブランド毎の以下の値を棒グラフにしたものです

  • ブルー棒｜平均価格
  • レッド棒｜平均レビュー評価

「cc」というブランドが高価格でありながら、他のブランドに比べレビューの評価が「3.7」と低めです
高価格であることによるユーザーの期待に、十分に応えられていない可能性がありそうです

価格毎の レビュー投稿数と 商品数

53684368763854.png

上記グラフは

  • 左が低価格商品。右に行くほど高価格商品
  • ブルー棒｜レビューの平均投稿数
  • オレンジ線｜各価格帯の商品数

となっており、高価格商品になるにつれ（グラフ右に行くにつれ）、レビュー投稿数が多いことが分かります

またグラフ左右を比べると、グラフ左側（低価格帯）は、商品数（オレンジの線）に対し、レビューの平均投稿数（ブルーの棒）が少なめになっています。つまり「高価格の商品ほどレビューが投稿されやすく、低価格の商品はレビューが投稿されずらい」と言えます

高価格の商品の方が低価格商品より熱を持って購入され、レビューに対するモチベーションも高くなるのではないか、と考えることができそうです

総括

約30,000件の商品データを、BigQueryで集計、Googleデータポータルで可視化・分析し、

  • wcというブランドがレビューの獲得にとくに成功している
  • ccというブランドが、高価格であることの期待に応えられていない可能性がある
  • 高価格の商品ほどレビューが投稿されやすい

など、特徴・傾向を把握することができました。それをもって、

  • ccブランドの満足度向上に取り組む
  • 低価格商品のレビュー獲得手段を検討する

など、次の施策検討に活かすことができるかと考えます

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2019-05-06T01:19:17+09:00

【BigQuery】 商品データを分析・可視化

12d696d6167652d73746f72652e73332e616d617a6f6e6177732e636f6d2f302f3132303839382f30313366663136332d333730642d373335382d376363612d38393466373932.png

概要

ECサイトの商品データ約30,000件をBigQueryで集計、Googleデータポータルで可視化・分析し、データの特徴や傾向把握を試みます

対象データ

今回対象とするのは、「Amazonにおけるインナーウェア製品データ」です

i01.png

スクリーンショット 2019-05-05 18.01.04.png

上記のように、「商品名／価格（ドル）／ブランド名／レビュー件数..」等のデータが約30,000件入っています
Kaggleにて公開されているので、CSVファイルとしてダウンロードしてきます

※実際のデータでは実在のブランド名が使われていますが、この記事内では全て仮名に置き換えています

▼ BigQueryで集計

データの用意

まずはBigQueryにデータを入れていきます

データセット作成

「データセットを作成」クリック

i02.png

データセットIDを入力

i03.png

i04.png

プロジェクトに、空のデータセットが作成されました

テーブル作成

「テーブルを作成」をクリック。データソースとしてCSVファイルをアップロード

i05.png

i06.png

CSVファイルが読み込まれ、テーブルが作成されました

SQL

現時点でデータは約30,000行

ただ、数が多いのは分析対象であるインナーウェアの「色ごと／サイズごと」で行が分けられているためで、Amazonでは色／サイズが違っても「1商品扱い（ひとまとめにレビューされる）」で設定されている場合が多くあります

今回のインナーウェアも、商品によっては30バリエーション以上ある商品もありますが、それも「1商品扱い」となります

今回「1商品扱い」となる条件は以下と設定します

  • ブランド名が同一
  • 商品名が同一
  • レビュー：評価が同一
  • レビュー：件数が同一

以上を踏まえ、SQLで商品情報の重複（同じ商品のバリエーション違い）をグルーピングします

SELECT
  brand_name,
  product_name,
  rating,
  review_count,
  TRUNC (AVG (price)) price_avg
FROM
  `practice-01-234805.amazon_products.az_innerwear`
GROUP BY
  brand_name,
  product_name,
  rating,
  review_count
ORDER BY
  brand_name,
  product_name,
  review_count
  DESC
;

「1商品扱い」でも、価格が違う場合があるので（とてもややこしいですが…）、価格に関してはグルーピングした「平均」を着地とし、TRUNC関数で小数点以下を切り捨てています

i07.png

「色／サイズ違い」をひとまとめにした結果、約30,000行あったデータが約400行にまとまり、各商品の平均価格が算出できました

▼ Googleデータポータルで可視化

BigQueryで集計したデータを、Googleデータポータルで可視化し、分析していきます
「データポータルで調べる」をクリック

i08.png

Googleデータポータルにデータが引き継がれます

i09.png

グラフ選択

分析に適したグラフを選択

i10.png

ディメンション／指標設定

分析したいディメンションと指標を設定

i11.png

可視化 結果

今回は各ブランドごとのレビュー数の比較や、価格帯ごとの商品数やレビュー数の比較等を行います

ブランド割合／レビュー投稿数の割合

egt57rst8.png

左の円グラフ「ブランド割合」で見ると、商品全体の半数以上を「wc」というブランドが占めており、次に「ck」というブランドが続きます

さらに、右の円グラフ「レビュー投稿数の割合」では「wc」が7割以上を占めており、このブランドはレビューの獲得にとくに成功していると言えます

ブランド毎の平均価格とレビュー評価

スクリーンショット 2019-05-03 18.39.18.png

上記は、ブランド毎の以下の値を棒グラフにしたものです

  • ブルー棒｜平均価格
  • レッド棒｜平均レビュー評価

「cc」というブランドが高価格でありながら、他のブランドに比べレビューの評価が「3.7」と低めです
高価格であることによるユーザーの期待に、十分に応えられていない可能性がありそうです

価格毎の レビュー投稿数と 商品数

53684368763854.png

上記グラフは

  • 左が低価格商品。右に行くほど高価格商品
  • ブルー棒｜レビューの平均投稿数
  • オレンジ線｜各価格帯の商品数

となっており、高価格商品になるにつれ（グラフ右に行くにつれ）、レビュー投稿数が多いことが分かります

またグラフ左右を比べると、グラフ左側（低価格帯）は、商品数（オレンジの線）に対し、レビューの平均投稿数（ブルーの棒）が少なめになっています。つまり「高価格の商品ほどレビューが投稿されやすく、低価格の商品はレビューが投稿されずらい」と言えます

高価格の商品の方が低価格商品より熱を持って購入され、レビューに対するモチベーションも高くなるのではないか、と考えることができそうです

総括

約30,000件の商品データを、BigQueryで集計、Googleデータポータルで可視化・分析し、

  • wcというブランドがレビューの獲得にとくに成功している
  • ccというブランドが、高価格であることの期待に応えられていない可能性がある
  • 高価格の商品ほどレビューが投稿されやすい

など、特徴・傾向を把握することができました。それをもって、

  • ccブランドの満足度向上に取り組む
  • 低価格商品のレビュー獲得手段を検討する

など、次の施策検討に活かすことができるかと考えます

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

日付別

タグ一覧