sql 日別 集計 13

乱数で検証データを生成する 7. 手を動かしながら書きたい方はこちらを参考にしてサンプルをご用意ください。, ポイントは3-7行目のcase式です。 上記を from にし、年月日を group by して こんにちは、PairsのAnalyzeチームでエンジニアをしている鉄本です。 Analyzeチームでは、施策検討時のデータ抽出やサービス上の問題検知のために、分析用途のSQLクエリ (MySQL) を作成しています。今回は、日々の業務を通して学んだTipsやよく使うクエリの一部を、簡単な活用例と一緒に前編・後編の2回に分けてお話ししたいと思います。前編では分析でよく使うクエリを中心にご紹介します。 この記事を参考に、分析用SQLの高度な運用に役立てていただけたら幸いです！ 本記事で紹介するTipsで用いる関数の詳細については、公式リファレンスを参考にしてください。, 1. あとは年月日ごとに count すればいける感じ!? リンクを開いてもらうとER図が載っているので参照してください。, rentalテーブルに保持しているレンタル履歴データとinventoryテーブル・filmテーブルを結合し、月毎・フィルム毎のユニークユーザ数と、各月のユニークユーザ数を抽出しました。そして抽出したデータを、縦方向にはフィルムID(film_id)・タイトル(title)を、横方向には年月を持つクロス集計とします。注意点としては、各月のユニークユーザ数は月毎・フィルム毎のユニークユーザ数の合計とならないことです。, まずはテンポラリテーブルの作成についてです。上記にも書きましたが、これを行う目的は一時的なテーブルにデータを保持することでクエリを短く・単純にすることです。今回はデータの抽出に必要なテーブルの結合、及び項目の絞り込みを予め行い、テンポラリテーブルに格納したいと思います。では、先に書いたrentalテーブル・inventoryテーブル・filmテーブルを結合し、テンポラリテーブルに格納します。, 上記の3テーブルを結合し、必要な項目を抽出しています。また月毎のデータが必要であるため、「rental_month」という列名で予めレンタルした年月を取得するようにしています。この結果作成されるテンポラリテーブルは以下の通りです。, 実際はもっと件数が多いのですが、分かりやすくするため一部を抜粋しております。このテンポラリテーブルからデータを抽出します。, 真ん中のサブクエリで、月毎・フィルム毎のユニークユーザ数と、各月のユニークユーザ数を抽出しています。このサブクエリに外側でORDER BYを掛けて、年月・film_idで並び替えています。この結果は以下の通りです。, このままでも求めたい値は算出できているのですが、今回はこの結果を元にクロス集計を行います。なので値を算出するクエリとクロス集計を作るクエリを分けるため、この結果もテンポラリテーブルに登録します。以下がテンポラリテーブルを作るSQLとなります。, 先に紹介したSELECT文に「CREATE TEMPORARY TABLE・・・」を追加しただけですね。, 最後にクロス集計です。実はクロス集計は以前の記事で書いた「縦持ちのデータを横持ちに入れ替える」と同じやり方となります。以下の様なSQLとなります。, 一番最初に示した2次元の表に近づいたかと思います。ただしこの方法の欠点としては、年月を動的に増やすことができないことです。また年月の表現に全角文字を使用していますが(使用したデータベースが半角数値からカラム名を始めることができないため)、ここも個人的には気になるところです(私がやったことですが・・・)。, テンポラリテーブルを使用してSQLを分割し、クロス集計を行う例について見てきました。バッチ処理等では長いSQLが多くなりがちだと思いますが、その場合はテンポラリテーブルの導入を考えてもいいかもしれません。またクロス集計についても、以前書いたSQLによる縦持ち・横持ちを入れ替えることの実例として見て頂ければ幸いです。, 今回の内容については、以下の本を参考にさせて頂きました。ありがとうございました。 クロス集計とは、与えられたデータの中から複数個の項目を抽出してデータ分析や集計を行う手法のことである。 グルーピングした1番古い(新しい)レコードの情報を取り出す, id registration_date last_login_date 1 2016–07–31 2016–08–07 2 2016–08–01 2016–08–07 3 2016–08–01 2016–08–16 4 2016–08–02 2016–08–06, ここで、「2016/08/01以前に登録していて、最後のログインが2016/08/01から1週間以内のユーザーを抽出したい」と依頼を受けたとします。 まずは何も考えず、クエリを作ってみます。, ここで「日付を変更したい」と言われたら、3箇所書き換える必要がありますよね。 集計期間を変更するたびに全て書き換えていたら面倒ですし、変更漏れで間違った分析をしかねません。 そんなとき「ユーザー定義変数」を使うと、書き換えるのは1箇所だけで済みます。, ユーザー定義変数は「@」をつけて以下のようにSET文で定義することで、クエリの中で使えるようになります。 ただし、ユーザ定義変数はクライアントセッションに固有の値のため、他のセッションから参照することはできず、クライアントセッションが終了すると自動的にリセットされますのでご注意ください。, id registration_date last_login_date 1 2016–07–31 2016–08–07 2 2016–08–01 2016–08–07, 生年月日から、ある時点での年齢を算出したいケースは度々見られると思います。シンプルな解決方法では、「年齢を算出したい日付と生年月日との差分の日数を取り、365日で割る」という方法が考えられます。, しかしこの方法だと、うるう年が考慮されていないため誤差が発生してしまいます。そこで私は以下のクエリで計算するようにしています。, id date event_name 1 2016–08–01 lesson1 2 2016–08–03 lesson2 3 2016–08–03 lesson3 4 2016–08–04 lesson4, ここで、「イベントが無い日は0件として、件数を日次で集計して欲しい」と依頼を受けたとします。件数の集計は以下のクエリでできますが、0件の情報を持つ事はできません。, date event_count 2016–08–01 1 2016–08–03 2 2016–08–04 1, このような時は、「カレンダーテーブル」を使って対応することができます。カレンダーテーブルとは、以下のように日付だけを列挙したテーブルです。, date 2016–08–01 2016–08–02 2016–08–03 2016–08–04 2016–08–05 2016–08–06 2016–08–07, 考え方として、まずカレンダーテーブルを作成し、eventテーブルと外部結合することで、eventテーブルに存在しない日付を集計に含めることができます。, 以下の構成で、連続する数字を生成します。 (1) @numに初期値を定義する SELECT文 (2) @numを1ずつ加算する SELECT文 (3) SELECT文の結果を結合する UNION ALL (4) 必要な件数(日数)を取得する LIMIT (2)のSELECT文で指定するテーブルはなんでも良いですが、LIMITで指定する件数以上のレコード数が必要です。今回は、MySQLでは必ず存在するテーブルとしてinformation_schema.COLUMNSを指定しています。, 生成した連続する数字を、DATE_ADD()関数で指定日からの経過日数として加算することで連続する日付を取得して、仮想のカレンダーテーブルを生成することができます。, 生成したカレンダーテーブルとeventテーブルを外部結合します。カレンダーテーブルの日別ごとに集計をすることで、eventテーブルにレコードがなくても、0件として集計することができます。, date event_count 2016–08–01 1 2016–08–02 0 2016–08–03 2 2016–08–04 1 2016–08–05 0 2016–08–06 0 2016–08–07 0, これでデータがない日を0件で補完することができました。 次の章では、この仮想のカレンダーテーブルを応用した分析を紹介します。, あるデータの件数を日次で集計して、数値が増加傾向か、減少傾向かを把握したいことってありますよね。 しかし、1日あたりの件数が少なかったり、特定の曜日だけ件数が多い場合など、日によってばらつきの大きいデータの場合、単純な日次集計グラフでは増減傾向がパッと見で分からないことがあります。, そんなとき、とある日を起点とした過去n日分の結果を合計した結果を使うことで、データをならして傾向を見やすくする方法があります。FXなどのチャート分析ではよく使われる手法で、この方法で出したグラフは移動平均線と呼ばれます。 では、先ほどのカレンダーテーブルを少し工夫して、実際に移動平均線を出してみましょう！過去m日間のn日移動平均を分析したいとして、組み合わせて使うためのn*mテーブルを作ってみます。, day1 day2 2016–08–27 2016–08–27 2016–08–27 2016–08–26 2016–08–27 2016–08–25 2016–08–27 2016–08–24 2016–08–27 2016–08–23 2016–08–27 2016–08–22 2016–08–27 2016–08–21 2016–08–26 2016–08–26 2016–08–26 2016–08–25 2016–08–26 2016–08–24 2016–08–26 2016–08–23 2016–08–26 2016–08–22 2016–08–26 2016–08–21 2016–08–26 2016–08–20 … …, day1の日付に対応する過去n日分の日付の一覧をday2で取得することができます。このテーブルを使い、実際にデータを出してみましょう。, 先ほどのn*mテーブルを利用して、そのまま日別の件数を出したものと、7日間移動平均で出したものを比べてみましょう。, グラフにしてみると、移動平均線が滑らかになって傾向が見やすくなったことがわかりますね！Pairsではこれを応用して、DailyMAUという「過去30日間のログインのユニークユーザー数」を指標においてサービスの状況を監視しています。 なお、このクエリはテーブルの構成やデータ量によっては、かなり重い処理になりますので、開発環境で検証してから使うようにしてください。, 今回は分析で役に立つTipsを4つ紹介しました。皆さんも、是非業務で活用して、分析作業の幅を広げてください。, 後編では、テストデータ作成のためのマスキング処理、メールアドレスの便利な集計方法、マイナーだけど便利な関数、そしてMySQLでハマりがちな罠とその対処法などについて紹介いたします。, SELECT id, registration_date, last_login_date. PostgreSQLでログ件数などを時刻から月別、曜日別に集計します。 そのためのSQLの作り方メモです。 やりたいこと 下記のような「log」テーブルを想定し、user_id毎に「log」テーブルのレコードの件数を集計 … ージャおよびタイプ・リファレンス』. 4 / クリップ SQL(SQL Server)で日付を持ったデータの集計をしています。 開始日時と終了日時を持ったデータに対し、1ヶ月分、23時59分59秒に、 開始されていて終了されていないデータの件数を一覧にしたいのですが うまい方法が思いつきません。 データはごく簡単に書くと以下のようなテーブルで SET @now = '2016-08-30 13:00:00', @birthday = '2000-08-31'; SELECT date, COUNT(event_name) AS event_count, SELECT @num := 0 AS number -- (1)@numに初期値0を定義する, SELECT calendar.date, COUNT(e.id) AS event_count, Animations challenges #1 — Bear iOS Search animation, A Quick Deep Learning Recipe: Time Series Forecasting with Keras in Python, How to do time series forecasting in BigQuery, Understanding the Hierarchy of Campaign Management, How to Conquer Cohort Analysis With a Powerful Clinical Research Tool, Time Series Forecasting with LSTMs using TensorFlow 2 and Keras in Python. みなさんこんにちは。フリーランスプログラマーのsatoです。 今回は、グループ化を行う「group by」句について見てきましょう。 これを使いこなせれば、種類ごとに集計をかけることが簡単にできるようになりますよ! テーブルと(必要な分の)日付マスタを cross join して 該当レコード(マスタ由来の日付が範囲内にある)を選別して 7日移動平均でデータの増減傾向を割り出す - 後編 - 5. このCASE式によりGroup by句で分けられたグループごとに、③の条件が合った時だけamountが足されていくことになり、２月だけの値を縦で表示することができまます。 1, 回答 すなおにループしますね。自分だったら。, こんばんは。 2行目 date_trunc()関数の結果が SQL Serverで集計表を出力 日付をDATETIME型で管理している場合、CONVERT関数、DATEPART関数を使って、日、週、月、年別の集計結果を出力することができます。 icon-chevron-right 週別の集計は日曜はじまりなので注意する 集計方法 日次集計 [crayon 1か月分が当月なのか前月なのか任意なのかがわからないので - date_trunc('day',payment_date) →　 2007-02-15 00:00:00 - date_trunc('hour',payment_date) →　 2007-02-15 22:00:00, date_trunc('month',payment_date)とすれば、 この際、開始時刻は年月日の形式に変換しておく。 PostgreSQL Sample Database まずそもそもクロス集計とはなんなのか？ 6行目 このカラムの値は'２月'とする PostgreSQLでログ件数などを時刻から月別、曜日別に集計します。 教えてください。3日ほど、悩んでおります。オラクルのSQLにおいての累計値の取得がどうしてもできません。会社毎に、毎日の売上累計をSQL一発で取得したいです。下記例の【★売上累計金額（会社別）】の箇所をレコード毎につみあげ Help us understand the problem. - date_trunc('month',payment_date) →　 2007-02-01 00:00:00 Why not register and get more from Qiita? 7日移動平均でデータの増減傾向を割り出す, 5. うまい方法が思いつきません。, 日付マスタのようなものはあるので、そこで1ヶ月分の日付のリストを作成し、LEFT OUTER JOIN させるようなことを考えています。, teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。, 評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。, 上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。. 複数行の結果をカンマ区切りで1行に集約する 8. 昔からある関数で基本ですが、昨日time型を使ったついでに。時系列集計で1時間とか1日単位など単純な場合、↓ こんな風にdate_trunc関数で丸めて集計するのが手軽です。関数名はdateとあるけど時刻単位も可。ドキュメントはこちら。 SQL Serverで月別集計. 日付マスタ(必要な分)を組み合わせればいけそうですが…, 正直なところ、そこまで面倒なことをするくらいなら 5行目 '2007-02-01'と等しくない時は0を渡す メールアドレスのドメインを集計する 6. とりあえず手入力で書いておきます, 予めカレンダーテーブルを用意しておかないかぎり、 3 / クリップ そのためのSQLの作り方メモです。, 下記のような「log」テーブルを想定し、user_id毎に「log」テーブルのレコードの件数を集計します。, 'month'(月)のほかにも'hour'(時)、'century'(世紀)なども使えます。, 'dow'(曜日)のほかにも'day'(日)、'decade'(十年)なども使えます。. データのない日を補完して日別レポートを作成する 4. この箇所は検索条件のTO条件と一致させても良いと思います。, 一応月内の最終日まで表示することを考慮して月末日とは書いておりますが･･･ 開始されていて終了されていないデータの件数を一覧にしたいのですが 予め10年分くらいの有効日付を投入しておけばよいかと. 分析系のSQLを書くことが多くなってきたので学んだことを書いていきます。 まずそもそもクロス集計とはなんなのか？ クロス集計とは、与えられたデータの中から複数個の項目を抽出してデータ分析や集計を行う手法のことである。Weblio辞書２つ以上の項目を掛け合わせて、その項目の相関関係を表にして視覚的にわかりやすく表現しているものだそうです。 ３個以上の項目を使う多重クロス集計というのもあるそうですが、今回SQLで実現するのは２個の項目を使用したクロス集計になります。 By following users and tags, you can catch up information on technical fields that you are interested in as a whole, By "stocking" the articles you like, you can search right away. クエリ内の複数箇所で使われる固定値を「ユーザ定義変数」でまとめる 2. プライマリ・コンテンツに移動, 「図18-2 スライド・ウィンドウの例」の説明, 例: ランキング関数でのNULLの処理, データ・ウェアハウスにおける集計のためのSQL, 「図18-3 バケットの割当て」の説明, CASE文を使用したヒストグラムの作成, ビジネス・インテリジェンス問合せの例4: 高頻度項目セット. 「group by」とは 「group by」の使い方 「where」で集計【前】を絞ろう A.Ichiさん掲示のコードのようにCOUNT(開始日時)で事足りますね。, 期待する結果から考えるとこんな感じでしょうか? マスタ由来の日付で gropu by して count した結果と データの無い日付を抽出することはできません。 3行目 '2007-02-01'のときは teratailを一緒に作りたいエンジニア. PostgreSQLで、日付別に集計したい場合の質問です。 ここでは簡単に、下記のようなテーブルがあるとします。 CREATE TABLE おこづかい帳( id serial, 購入日時 timestamp without time zone NOT NULL, 購入 … Weblio辞書 ことが挙げられます。なのでサブクエリなどを使っていくと、一つのクエリが長くなりがちです。これについては「テンポラリテーブル」を使用して一時的なテーブルにデータを保持させることで、一つ一つのクエリを短く・複数に分割することが可能です。, またデータの表示方法として以下の様な2次元の表というのは非常に分かりやすいかと思います。, このような縦・横方向に項目を持つ集計を「クロス集計」と言います。が、SQLで単純にSELECTした場合、取得した項目を縦方向に保持してしまいます。, 今回は「テンポラリテーブル」を使用してクエリを短くすることと、上記のような「クロス集計」をSQLで実現する方法について書きたいと思います。, 今回は以下のPostgreSQLのサンプルデータベースを使いました。DVDのレンタルショップを題材としたデータのようです。 メールアドレスのドメインを集計する 6. 0, 回答 記事が投稿された日付ごとのデータを収集したいと考えていました。 SQLを使って日別で集計する データベースではdatetime型で保存していたため、投稿日時でgruop byしてしまうと下図のように時間まで含めてグループ化されてしまいました。 select PublishTime as "date", count(Ar… 指定された精度で切り捨てます。今回は'month'を指定したので月より後ろが「01 00:00:00」(1日の0時)として切り捨てられています。, 日付/時刻の値から年や時刻などの部分フィールドを抽出する。今回は'dow'を指定したので日曜日~土曜日が「0~6」として返却されます。, 条件式を記載できます。今回は月別では切り捨てた月初日であれば1、それ以外は0として、曜日別では各曜日で１or０を返却しています。, you can read useful information later efficiently. 誕生日(DATE)から現在の年齢を計算する 3. SQlite3で、年、月、日のフィールドがわかれているテーブルの日付をbetweenで期間を指定して... 回答 1 / クリップ 1か月分の終了してないデータをサブクエリで絞り込む。 10年戦えるデータ分析入門. ... とりあえず、下記のSQLで201401月以降のデータを出すことには成功したのですがそこから先で躓いています。 ヤリタイことは、エクセルを併用して何とか出来たのですが ... 2016/04/28 13:51 Copyright © 2001, 2017, Oracle and/or its affiliates. All rights reserved. 0, 【募集】 ２つ以上の項目を掛け合わせて、その項目の相関関係を表にして視覚的にわかりやすく表現しているものだそうです。, ３個以上の項目を使う多重クロス集計というのもあるそうですが、今回SQLで実現するのは２個の項目を使用したクロス集計になります。, 今回はサンプルのデータベースのpaymentテーブルを使って説明します。 SET @name1 = 'hoge', @name2 = 'fuga', @name3 = 'piyo'; SELECT @name1 := 'hoge', @name2 := 'fuga', @name3 := 'piyo'; // => hoge fuga piyo. 開始日時と終了日時を持ったデータに対し、1ヶ月分、23時59分59秒に、 同じことを３、４、５月にもしています。, 他のDBではすでにある機能のようですが、postgreSQLでも9.5以降ならrollup関数を使い合計を出すこともできます。, データ分析に使うSQLは楽しいですね。今後も掘り下げて記事にしてみようと思います。. テンポラリテーブルを使用してsqlを分割し、クロス集計を行う例について見てきました。バッチ処理等では長いsqlが多くなりがちだと思いますが、その場合はテンポラリテーブルの導入を考えてもいいかも … 投稿 2016/07/27 16:09 データのない日を補完して日別レポートを作成する 4. この手順では、製品集計の各レベルで13番目の月の売上データを作成します。メインの問合せでは、分析関数sumがcase式とともに使用され、各四半期の最初の月における売上合計として定義された13番目の月が計算されます。 ・編集 2016/07/27 16:47, SQL(SQL Server)で日付を持ったデータの集計をしています。 date_trunc()は非常に便利で、第１引数でmonthと指定するとそれ以下の数字をdayと指定すればそれ以下を切り捨ててくれます。 4行目 amountを渡す sqlでグルーピングしてやや複雑な集計する方法こういうことってやりたい時あるよなーと思いつついい方法が思い浮かばないので質問させてください。日付ごとにタイプなどの条件により集計して、1行で1日分のデータを並べて出力したいと思