質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.53%

  • PostgreSQL

    1057questions

    PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

過去5年間の移動年計を出したい

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 195

m6u

FuelPHP総合1位

 前提・実現したいこと

PostgreSQLのデータベースにある、
売上伝票データの収まるテーブルを使って、
1年間の買上金額を基にランク分けして、
A,B,C,Zランクに何人いて買上額がいくらなのか、
これを過去5年間月単位の移動年計にしようとして躓いています。

指定日を基準に過去1年間に対するデータを抽出するSELECT文が出来上がっています。
これに、generate_series関数を組み合わせて基準日をスライドさせれば
移動年計になると思いついたのですが。

 指定日を基準日としたランク&買上額集計SQL

カラム 意味
jrn_kyk_cd integer 顧客コード
jrn_syo_gak integer 買上額(売上時は正、売上取消は負)
jrn_dnp_nm text 伝票名、売上と客返(売上取消)
jrn_ins_ymd timestamptz 記録日時(create_atみたいなもの)

※ 売上の取消ではレコード削除が行われず、売上と逆のマイナス金額が入力される。

select j.k_rank, count(j.jrn_kyk_cd) as k_count, sum(j.syo_gak) as gak
from (
  select jrn_kyk_cd, sum(jrn_syo_gak) as syo_gak, rank() over (order by sum(jrn_syo_gak) desc) as rnk, case when sum(jrn_syo_gak) >= 300000 then 'A' when sum(jrn_syo_gak) >= 200000 then 'B' when sum(jrn_syo_gak) >= 100000 then 'C' else 'Z' end as k_rank
  from jrn_tbl
  where jrn_pst_cd = 1 and (jrn_dnp_nm = '売上' or jrn_dnp_nm = '客返') and jrn_ins_ymd between '2017-06-01+09'::timestamptz and '2018-06-01+09'::timestamptz 
  group by jrn_kyk_cd
) as j
group by j.k_rank
order by j.k_rank

 試したこと

これを拡張して、

select s.a, j.k_rank, count(j.jrn_kyk_cd) as k_count, sum(j.syo_gak) as gak
from (
  select s.a, jrn_kyk_cd, sum(jrn_syo_gak) as syo_gak, rank() over (order by sum(jrn_syo_gak) desc) as rnk, case when sum(jrn_syo_gak) >= 300000 then 'A' when sum(jrn_syo_gak) >= 200000 then 'B' when sum(jrn_syo_gak) >= 100000 then 'C' else 'Z' end as k_rank
  from jrn_tbl
  where jrn_pst_cd = 1 and (jrn_dnp_nm = '売上' or jrn_dnp_nm = '客返') and jrn_ins_ymd between s.a + '-1 year'::interval and s.a 
  group by s.a, jrn_kyk_cd
) as j, (
  select a
  from generate_series('2018-06-01+09'::timestamptz, '2018-06-01+09'::timestamptz + '-5 years'::interval, '-1 month'::interval) as a
) as s
group by s.a, j.k_rank
order by s.a, j.k_rank


などと以前作ったクエリーからgenerate_series関数を試しに組み合わせてみたものの、

SQL   : missing FROM-clause entry for table "s"

などと。

 補足情報(FW/ツールのバージョンなど)

PostgreSQL 9.6.8

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 1

check解決した方法

0

質問文を起こしている最中に自己解決しちゃったので、
せっかく書いた質問文がもったいないから、
自己解決として投稿しておきます。

select j.a, j.k_rank, count(j.jrn_kyk_cd) as k_count, sum(j.syo_gak) as gak
from (
  select s.a, jrn_kyk_cd, sum(jrn_syo_gak) as syo_gak, rank() over (order by sum(jrn_syo_gak) desc) as rnk, case when sum(jrn_syo_gak) >= 300000 then 'A' when sum(jrn_syo_gak) >= 200000 then 'B' when sum(jrn_syo_gak) >= 100000 then 'C' else 'Z' end as k_rank
  from jrn_tbl, (
    select a
    from generate_series('2018-06-01+09'::timestamptz, '2018-06-01+09'::timestamptz + '-5 years'::interval, '-1 month'::interval) as a
  ) as s
  where jrn_pst_cd = 1 and (jrn_dnp_nm = '売上' or jrn_dnp_nm = '客返') and jrn_ins_ymd between s.a + '-1 year'::interval and s.a 
  group by s.a, jrn_kyk_cd
) as j
group by j.a, j.k_rank
order by j.a, j.k_rank

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.53%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • PostgreSQL

    1057questions

    PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。