BinaryDevelop
  1. ホーム
  2. データベース
  3. ポストグレスキュー

Postgresqlの行から列への高度な応用と要約のアイデア

2022-01-10 13:16:46

前置き

休日前に会社のビジネス面では、統計レポートを作成する必要があり、このレポートは、月の推定いくつかのスター製品の売上をカウントするために使用され、私たちのデータは、行によって格納されています Date|Product|Channel|Sales このように、また奇妙な言った、我々はレポート（関）テーブル（元）システム（ラ）システム（ジ）を簡単に実装することはできません買った。だから私は見て、それについて考え、それがSQLを介してこのようなレポートを計算することが可能であることがわかった（postgresqlの高次関数のおかげで？ 私は、フロントエンドの表示に興味がある学生は、メッセージを残すことができるように、以下のSQLの主要部分を記述します、あなたはハについて話をするセクションを作って考えることができますか？~

報告書作成

まず、業務上、こんな感じのレポートが必要なのですが、大丈夫そうですハァ〜。

次に、私のテストスクリプト（すべてテスト済み、バグなし）を紹介します。

テーブル構造 

drop table if EXISTS report1 ;
CREATE TABLE "report1" (
  "id" numeric(22) NOT NULL,
  "date" date NOT NULL,
  "product" varchar(100),
  "channel" varchar(100),
  "amount" numeric(20,4)
);

テーブルコメント

<テーブル フィールド 説明 イド 主キー 日付 日付 製品 製品 チャンネル チャンネル 量 売上高

テーブルデータ 

INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES (' 2105051726328010100000', '2021-05-04', 'product1', 'jingdong', '8899.0000');
INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES (' 2105051726328010100001', '2021-05-04', 'product2', 'jingdong', '99.0000');
INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES (' 2105051727068010100010', '2021-05-04', 'product1', 'tmobile', '230.0000');
INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES (' 2105051727068010100011', '2021-05-04', 'product2', 'tmobile', '9.9000');
INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES (' 2105051727068010100011', '2021-05-04', 'product3', 'offline store', '10.1000');
INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES (' 2105051727068010100000', '2021-05-04', 'product1', 'other', '10');
INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES (' 2105051727068010100099', '2021-05-04', 'product2', 'other', '20000');
INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES (' 2105051727068010100033', '2021-05-01', 'product1', 'other', '20000');
INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES (' 2105051727068010100044', '2021-05-01', 'product3', 'offline store', '12345');

振り返り

これを読んでいる人は、ちょっと考えてみてください。冒頭で、私たちのデータのベースは date|product|channel|sales 上のスクリーンショットを見た後、開封報告のスクリーンショットを見ると、私と同じように、以下のような点が分析できると思います。

  • レポートの縦書き表示は、大きく3つのパートに分かれています。

1部は前日の製品売上内訳
そして次のセクションは、前日のプロダクトチャンネル商品合計です
最後はチャネル別の月間統計です

  • レポートは水平方向に2つのパートに分かれています。

1部は前日のデータ
もう1つは、1ヶ月の合計データです。

最後のセクションは、全チャネルの商品合計、日間合計、月間合計です。

さて、ここで問題です、どうやるんですか？まず、あなたのSQLは大きく2つの部分（2つのサブクエリ）に分かれていることが非常によくわかります。

一つは前日のデータ、もう一つは一ヶ月の集計データ

データの最後の部分は、ジョイントテーブルクエリを行う必要があり、これはとても素晴らしいです、合計の最後の行のように、レポートの80％を完了しているようだ、ここで最初に嘘つきハ〜を販売する。

データの最初の部分（前日のデータ）

すぐにできるsqlの最初の部分は、行固有のカラム（これが一番実装しやすそうですね ?） 

select
  channel,
  sum(case product when 'product1' then amount end) as c1,
  sum(case product when 'product 2' then amount end) as c2,
  sum(case product when 'product 3' then amount end) as c3
from report1
group by channel ;

SQLは問題ないようですが、カラムが1つ足りないようです。 Total by channel day もちろん、postgresql のウィンドウ関数に慣れている人なら、ここで実装されている方法（ウィンドウの over 関数)で、SQL上で... 

select
  channel,
  day_sum,
  sum(case product when 'product1' then amount end) as c1,
  sum(case product when 'product 2' then amount end) as c2,
  sum(case product when 'product 3' then amount end) as c3
from
  ( select *,sum(amount) over (partition by channel) as day_sum from report1 where date=to_date('2021-05-04','yyyy-MM-dd') ) as t1
group by t1.channel ,t1.day_sum;

ははは、トップ画の day_sum 皆さんお馴染みですね、あははは〜。
無事完成したようです Daily data の部分ですが、ここでの難点はおそらく2つだけです。

  • 1つは、集計関数( sum ) + グループ化 ( group by ) を使って行ごとのカラムを行うことができます (もちろん postgresql 他にもとても良い行専用拡張機能があるので、ここでは紹介しません〜) もうひとつは、ウィンドウ機能( over ) を行うことで、事前に詳細な Aggregate by channel window こうすることで、チャンネル日の合計（行）が利用できるようになります〜。

どれだけ簡単か考えてみてください。次に、2つ目のデータの取得方法について見てみましょう。

データの第2部（月別プールデータ）

数ヶ月の集計データは、あなたがpostgresqlの日付処理をマスターすることに長けている場合、あなたは数分でそれを得ることができると推定され、ここで我々は大きなナイフを果たすことはありません、直接SQLを解放、ハハッ？ 

select 
  channel,sum(amount) as month_sum from report1 
where 
  date>=date(date_trunc('month',to_date('2021-05-04','yyyy-MM-dd'))) and date < date(date_trunc('month',to_date('2021-05-04','yyyy -MM-dd')) + '1 month') 
group by  
  channel

レポートデータ 最終解答

さて、データの2つの部分をチャンネル別に解くと channel するフィールド inner join 上記の2つのデータをマージすると、マージされたデータはおおよそ次のようになります。

<イグ

これはSQLです 

select
    ttt.channel,
    sum(tttt.day_sum) as day_sum,
    sum(ttt.month_sum) as month_sum,
    sum(ttt.c1) as c1,
    sum(tttt.c2) as c2,
    sum(tttt.c3) as c3
from (
        select tt1.*,tt2.month_sum from
        (
        select
        channel,
      day_sum,
        sum(case product when 'product1' then amount end) as c1,
        sum(case product when 'product 2' then amount end) as c2,
        sum(case product when 'product 3' then amount end) as c3
        from
        ( select *,sum(amount) over (partition by channel) as day_sum from report1 where date=to_date('2021-05-04','yyyy-MM-dd') ) as t1
        group by t1.channel ,t1.day_sum
        ) as tt1 left join
        (
          select channel,sum(amount) as month_sum from report1 where date>=date(date_trunc('month',to_date('2021-05-04','yyyy-MM-dd'))) and date & lt; date(date_trunc('month',to_date('2021-05-04','yyyy-MM-dd')) lt; date(date_trunc('month',to_date('2021-05-04','yyyy-MM-dd')) + '1 month') group by channel
        ) as tt2 on tt1.channel = tt2.channel
    ) ttt
GROUP BY ttt.channel
order by channel asc

ほら、集計データがすでにある、すでに最終結果とみなすことができる（あなたが集計行データを計算するためにレポートシステムが必要な場合）、もちろん、我々のレポートシステムはあまりにも面倒です（できないのではなく、あまりにも面倒）、あなたはそれを良い食べ物を与える必要があり、この時点で、それを行うにはどうすればよい。長い間、使っていないことを忘れているようです。 rollup 機能(最初は気づきませんでした(笑))を、試してみてください。 

select
    ttt.channel,
    sum(tttt.day_sum) as day_sum,
    sum(ttt.month_sum) as month_sum,
    sum(ttt.c1) as c1,
    sum(tttt.c2) as c2,
    sum(tttt.c3) as c3
    from (
        select tt1.*,tt2.month_sum from
        (
        select
        channel,
      day_sum,
        sum(case product when 'product1' then amount end) as c1,
        sum(case product when 'product 2' then amount end) as c2,
        sum(case product when 'product 3' then amount end) as c3
        from
        ( select *,sum(amount) over (partition by channel) as day_sum from report1 where date=to_date('2021-05-04','yyyy-MM-dd') ) as t1
        group by t1.channel ,t1.day_sum
        ) as tt1 left join
        (
          select channel,sum(amount) as month_sum from report1 where date>=date(date_trunc('month',to_date('2021-05-04','yyyy-MM-dd'))) and date & lt; date(date_trunc('month',to_date('2021-05-04','yyyy-MM-dd')) lt; date(date_trunc('month',to_date('2021-05-04','yyyy-MM-dd')) + '1 month') group by channel
        ) as tt2 on tt1.channel = tt2.channel
    ) ttt
    group by rollup(ttt.channel)
    order by channel asc

数字が合っているということは、成功したんだ～？

まとめ

努力と学習意欲があれば postgresql 世の中には素晴らしいものがたくさんあり、もちろんmysqlよりも少し面倒なものもありますが、学習する心があれば必ず乗り越えられますし、今でも beyond our own capabilities 私たちはまだ、自分たちの能力を超えたものを作ることができるんだ！ハハ、みんな頑張れ

次の章では、フロントエンドでセルをマージする効果をsqlで実現する方法についてお話します。すごいでしょう（ウェブ全体で探しても見つからないことを保証します）、転がらないことを祈ります、ハハハ〜。

行から列へのpostgresql高度なアプリケーションのこの記事＆amp;要約集計の実装のアイデアはここで紹介されて、より関連postgresql行から列の要約集計コンテンツは、スクリプトの家の前の記事を検索するか、次の関連記事を閲覧し続けることを願ってあなたは、よりスクリプト家をサポートします！.

関連

最新

おすすめ

について
/
プライバシー・ポリシー
/
お問い合わせ
© 2022 BinaryDevelop. すべての権利は、それぞれの所有者に帰属します