累計ってプログラム作るしかないと思ってたけど、SQLだけで普通にできるってことをいまさら知った。 会員数の推移を把握したい場合の例 usersテーブル id name created_at '1','user1','2014-10-20 18:17:53' '2','user2','2014-10-20 18:17:5… ひとつカラムを追加して、月次累計（Monthly Running Total）を求めたい場合に, こんな感じでSQLを書く。ここで、GROUP BY 1,2はGROUP BY t1.Date,t1.Salesと同じ意味。, ここでなぜsum(t2.Sales)が月次累計になるかというと、以下のように集計を外したSQLを書くと, こんな感じの結果セットが取得できる。（1日目は1レコード、2日目は2レコード、・・・31日目は31レコード）, これに対してsum(t2.Sales) GROUP BY t1.Date,t1.Salesをするので、月次累計となる。
select to_char(a.delivery_date,'yyyy/mm') 年月,a.product_id,a.PRODUCT_NAME,sum(a.price) from product_history awhere a.product_name = 'キャベツ'group by to_char(a.delivery_date,'yyyy/mm'),a.PRODUCT_ID,a.PRODUCT_NAMEorder by to_char(a.delivery_date,'yyyy/mm'); IT企業で勤務しています。 oracleのsqlについてご教授ください。 過去に遡って175日分を7日間ごとに集計したいのですが、以下ようなクエリしか思いつきませんでした。 もっと簡潔な方法がありましたら教えてください。 SQL Serverで集計表を出力 日付をDATETIME型で管理している場合、CONVERT関数、DATEPART関数を使って、日、週、月、年別の集計結果を出力することができます。 icon-chevron-right 週別の集計は日曜はじまりなので注意する 集計方法 日次集計 [crayon 以下のような商品の納入日を管理しているテーブルで、年月ごとの仕入価格を集計するSQLを考えたいと思います。. Oracle Database SQL言語リファレンス11g リリース1（11.1） Oracle SQL Developerユーザーズ・ガイド リリース3.1. select to_char(a.delivery_date,'yyyy/mm') 年月, 【SQL】GROUP BYとINNER JOINを使って最新日付のレコードを取得する, SQL基礎 明示的なデータ型の変換(TO_CHAR 日付から文字への変換) oracleデータベース, SQL基礎 INSERT、UPDATE、DELETE文の練習問題1 oracleデータベース. sqlクリニック（2）：日付データ演算の達人技を伝授する 【第1話】 (2/3) [山下正，株式会社インサイトテクノロジー]
よろしくお願いします。, auroralightsさんは、はてなブログを使っています。あなたもはてなブログをはじめてみませんか？, Powered by Hatena Blog
sql で数値型データの累積を取得する方法です。 例えば以下のように日ごとの売上実績数量や売上金額などを毎月の累積で取得したい場合などに便利です。
| 以下のような商品の納入日を管理しているテーブルで、年月ごとの仕入価格を集計するsqlを考えたいと思います。 ＜目次＞ データの準備 年月ごとのp_classの仕入価格合計を抽出 年月ごとのキャベツの仕入価格合計を抽出する データの準備 今回使ったデータは以下のsqlで作成できます。
このシリーズ記事のパート8 "状況に合った型の選択"（Oracle Magazine、2012年11/12月）では、よく使用されるSQL日付関数を紹介し、問合せを利用して、日付を表す結果セット・データの表示を変更する方法について説明しました。また、SYSDATE関数と日付計算を紹介し、よりわかりやすい結果を得るために、それらを利用して結果セット・データを操作する方法についても説明しました。このシリーズ記事でこれまでに取り上げた関数はすべて、単一行の結果に基づいて動作するものです。集計関数（別名グループ関数）は複数行に基づいて動作します。集計関数を使用すれば、データベース内での格納方法とは異なる表示になるようにデータを操作できます。この記事では、比較的よく使用されるSQLグループ関数のほか、GROUP BY句とHAVING句について紹介します。, このシリーズ記事の例を試すには、Oracle Databaseインスタンスにアクセスする必要があります。必要に応じて、お使いのオペレーティング・システムに対応したOracle Databaseエディションをダウンロードし、インストールしてください。筆者がインストールをお勧めするエディションは、Oracle Database, Express Edition 11g Release 2です。このOracle Databaseソフトウェアをインストールする場合は、データベースの作成と構成が可能なインストール・オプションを選択してください。サンプルのユーザー・アカウントと関連する新しいスキーマを含む新しいデータベースが作成されます（SQL_101は、このシリーズ記事の例で使用するユーザー・アカウントです。また、データベースの表やその他のオブジェクトが作成されるスキーマでもあります）。インストール・プロセスの実行中にスキーマのパスワードを指定するように求められたら、SYSおよびSYSTEMのパスワードを入力して確認し、そのパスワードを覚えておいてください。, 最後に、このデータベース・ソフトウェアをゼロからインストールした場合でも、既存のOracle Databaseインスタンスにアクセスする場合でも、SQLスクリプトをダウンロードして解凍し、実行して、この記事の例で必要となるSQL_101スキーマ用の表を作成します（このスクリプトをテキスト・エディタで開き、実行方法の説明を確認してください）。, Oracle Database SQL言語リファレンス11g リリース1（11.1）, すべての集計関数は、データをグループ化して、最終的に単一値の結果を生成します。集計関数は複数行の値に基づいて動作するため、集計関数を使用すれば合計などのサマリー・データを生成できます。たとえば、"全従業員に支払われる年間給与分配額の合計は？"といった予算計画の疑問に回答できます。リスト1の問合せは、この疑問に回答するためのSUM集計関数の使用例を示しています。この問合せは、EMPLOYEE表のSALARY列についてすべての値を合計するもので、その結果の合計値は970000です。, コード・リスト1：EMPLOYEE表のSALARYについて、すべての値の合計を表示, 集計関数を使用して回答できる業務上の疑問には、ほかにも“現在の全従業員の年間平均給与は？”というものがあります。リスト1の問合せと同様に、リスト2の問合せでもEMPLOYEE表のSALARY列に集計関数を適用しています。リスト2のAVG関数は、SALARY値を合計した後、その合計値を、SALARY値がNULLではない従業員レコード数で割ります。年間支払額合計の970000を従業員数10で割ると、年間給与の平均値は97000になります。, コード・リスト2：NULL以外のすべてのSALARY値を使用して給与の平均値を計算, EMPLOYEE表には11個のレコードがありますが、リスト2の給与の平均値計算で対象となっているレコードは10個のみです。これは、AVG集計関数ではNULL値が無視されるためです（EMPLOYEE表では、従業員Lori DovichiのSALARY値がNULLです）。NULL値に対してNULL以外の値を代用するために、このシリーズ記事のパート7で紹介したNVL関数・コールを、AVG関数コール内にネストできます（リスト3を参照）。リスト3で返される給与の平均値はリスト2で返される平均値よりも小さくなります。Lori DovichiのNULLのSALARY値が0に置き換えられて、その他のNULL以外のSALARY値とともに評価されるためです。NULL値に対してNULL以外の値を代用する操作は、業務上の観点から意味がある場合に限り使用してください。, 前回までのシリーズ記事で確認したとおり、SQL*Plusのset feedback onコマンドによって、問合せの条件を満たすレコード数が表示されます。この方法は、画面上に容易に表示できる少数のレコードがすぐに返されるような場合には有効です。しかし、数百、数千、あるいは数百万のレコードを評価する場合は不便です。結果セット内のすべてのレコードをデータベースからフェッチしてクライアントに返すまで待機する必要があるからです。もっと効率的な代替手段として、COUNT集計関数を利用できます（リスト4を参照）。, COUNT集計関数は、問合せの条件を満たすレコードの個数をカウントします。リスト4の問合せではCOUNT(*)を使用しています。COUNT(*)は、問合せの条件を満たす全行数を返す関数です。この結果、EMPLOYEE表内の全レコード数を取得できます。COUNT(*)ではNULL値が無視されませんが、COUNTに列名を指定した場合はNULL値が無視されます。リスト4とリスト5を比較すると、COUNT(*)によってEMPLOYEE表のすべての列を対象としてカウントする場合でも、COUNT(employee_id)によって主キー列のみを対象としてカウントする場合でも、同じ結果が返されています。, 一方、COUNT(*)またはCOUNT(employee_id)のコールを、COUNT(manager)のコールと比較してみましょう（リスト6を参照）。EMPLOYEE表には、11レコードのうち5レコードのMANAGER列に値がないため、返される単一の個数値にこれらのレコードは含まれません。, リスト7の問合せは、問合せ条件に一致する行がない場合、COUNT(*)またはCOUNT(column_name)のコールの結果、値0が返されることを示しています。この問合せは、雇用日が本日を表すシステム日付（SYSDATE）と一致するすべての従業員レコードについて、全行数と、すべてのMANAGER値の個数を表示するようにリクエストしています。問合せの実行日に雇用された従業員はいないため、個数の値として0が返されます。, コード・リスト7：一致する行がない場合にCOUNT(*)とCOUNT(column_name)は両方とも0を返すことを示す例, 重複を除いた値の個数を算出しようとしている場合は、COUNT集計関数とDISTINCTキーワードを組み合わせることができます。リスト8に、EMPLOYEE表内のDISTINCTまたはUNIQUE（DISTINCTの代わりに使用できるキーワード）を適用したMANAGER列の値をカウントする問合せを示します。その結果、3という個数が返されます。複数の従業員のMANAGER列がNULL値ですが、このNULL値は、COUNT集計関数のコールによって得られる重複を除いたMANAGER値の個数としてカウントされません。, 適切なソートを使用したSQL文によって行の値セットをフェッチした場合、その中の最大値と最小値を見つけることはたしかに可能です。しかし、結果セットが大規模で、最大または最小の結果のみが必要な場合は、結果セットの最上部や最下部までスクロールして結果を参照することは面倒でしょう。そのような場合は、代わりにMINおよびMAXという集計関数を使用できます。リスト9の問合せでは、これらの集計関数を使用して、EMPLOYEE表のSALARYの最大値と最小値を表示します。, この記事のこれまでの例では、特定の集計条件に一致するすべての行に基づいて動作する集計関数を取り上げました。しかし、場合によっては、データをさらに分類して集計する必要もあります。GROUP BY句を使用すれば、複数のレコードにわたるデータを収集して、1つまたは複数の列に基づいて結果をグループ化できます。集計関数とGROUP BY句は、グループごとの集計値を算出して返す目的で、一緒に使用されます。たとえば、リスト10の問合せでは、各部門の従業員数を取得します。, リスト10で注意すべき点として、EMPLOYEE表内には部門に所属していない従業員が1人いますが、その従業員も1つのグループとして結果に含まれています。また、この問合せではORDER BY句も使用しています。GROUP BY句はデータをグループ化しますが、結果を特定の順序でソートすることはありません。リスト11の問合せは、リスト10の問合せからORDER BY句を除いたものです。, GROUP BY句に続いてORDER BY句を記述する場合は、ORDER BY句に記述された列をSELECT構文のリストにも含める必要があります。リスト12の問合せは、SELECT構文の列リストとORDER BYの列リストが一致しない場合にエラーが発生することを示しています。同様に、SELECT構文のリストに含まれるが、集計操作に関係しないすべての列に対してGROUP BYを使用しない場合も、リスト13のようなエラーが発生します。リスト13の問合せはリスト12の問合せからGROUP BY句を除いたものです。, コード・リスト12：ORDER BY句の列リストがSELECT構文のリスト内に含まれない場合のエラー, 複数のグループを返そうとしている場合は、GROUP BY句が必要になります。そして、複数のグループを返すには、集計操作に関係しない列をSELECTリスト内に含める必要があります。リスト13の問合せで、そのような集計操作に関係しない列は、EMPLOYEE表のDEPARTMENT_IDです。集計関数を使用し、かつGROUP BY句を使用しない問合せでは、問合せの時点で問合せ対象の表に行が存在しないとしても、かならず1行返されます。, SELECT構文のリストでWHERE句を使用して結果セットをフィルタリングし、特定の条件を満たすレコードのみを含めることができるのと同様に、GROUP BY句でも同様の句を使用してグループをフィルタリングできます。そのためのHAVING句はGROUP BY句とともに使用できます。HAVING句により、指定した条件を満たすグループへと結果を絞り込むことができます。リスト14は、リスト10の問合せの拡張版です。リスト10の問合せにHAVING句を追加することで、従業員数が1人以下のグループを結果セットから除外できます。ご覧のように、部門が割り当てられていないグループはリスト14の結果セットには返されません。そのグループには1人の従業員しか含まれないためです。, HAVING句はおもに、集計関数を適用した列に基づいて動作します。一方、WHERE句は集計操作のない列やその他の式に基づいて動作します。ここでおもにと言ったのは、HAVING句ではフィルタリング操作内で複数の演算子を使用できるからです。たとえば、リスト15の問合せでは、次の2つの条件のいずれか一方を満たす部門ごと、給与ごとのグループにおける従業員数を表示しますが、集計関数が利用されているのは最初の条件だけです。, SELECT構文のリストに含まれるすべての列をGROUP BY句にも記述する必要がありますが、この制約は数値リテラルと文字列リテラル、定数式（列の値を使用しない式）、およびSYSDATEなどの関数には適用されません。リスト16に、例を示すためにリスト15の問合せを拡張した問合せを示します。この問合せのSELECT構文のリスト内には、リテラル、定数式、SYSDATE関数が含まれますが、これらの項目をGROUP BY句やORDER BY句に記述する必要はありません。, コード・リスト16：GROUP BYやORDER BYに含まれないリテラル、式、関数, このシリーズ記事で学習したほかのタイプの関数と同様に、集計関数も別の集計関数内にネストできます。リスト17の問合せでは、部門ごとに給与の合計を取得します。次に、部門ごとの給与合計の値にMIN集計関数を適用して、部門ごとの給与合計の最小値を取得します。この問合せでは、GROUP BY句に記述したすべての列をSELECT構文のリスト内に記述する必要はないことも示しています（この逆は必須であり、SELECT構文のリスト内に記述したすべての列はGROUP BY句に記述する必要があります）。, この記事では、よく使用される集計関数の一部と、それらの関数を使用してデータの表示を操作する方法について説明しました。MAX、MIN、AVGなどの単一グループの集計関数や、COUNT、SUMなどの複数グループの関数の使用方法を確認しました。データ内にNULL値が存在する場合のこれらの関数の動作や、その動作による結果への影響について確認しました。さらに、GROUP BY句とHAVING句について紹介し、これらの句を使用してサマリー・データをさらにフィルタリングして分類する方法についても説明しました。最後に重要なこととして、ORDER BY句をGROUP BY句とともに使用する場合に気をつけるべき落とし穴や、SELECT構文のリスト内に記述した列の値をGROUP BY句内にも記述する必要がある点について確認しました。この記事ではすべてのOracle Databaseの集計関数を説明していません。詳しくは、Oracle® Database SQL言語リファレンスのドキュメントを確認してください。このSQLの基礎に関する次回の記事では、分析関数について説明します。, Melanie Caffreyはオラクルの上級開発マネージャーです。Expert PL/SQL Practices for Oracle Developers and DBAs（Apress、2011年）およびExpert Oracle Practices: Oracle Database Administration from the Oak Table（Apress、2010年）の共著者でもあります。, 入力したキーワードの同義語を使用してください。たとえば、「ソフトウェア」の代わりに「アプリケーション」を試してみてください。.