MySQL5.6で最新の３日間、７日間、30日間の平均値を出力したいです

Question

###MySQL5.6で最新の３日間、７日間、30日間の店舗ごとの売上平均値を出力したいです
MySQL5.6で最新の３日間、７日間、30日間の店舗ごとの売上平均値を出力したいです。データは１日ごとに、ひとつのテーブルに入っています。

'SELECT AVG(A店売上), AVG(B店売上), AVG(C店売上), AVG(D店売上) FROM 売上一覧'

この続きをどう書けばよろしいのでしょうか。
SQL文が分かる方、ご教授くださいませ。よろしくお願いします。

追記
後出しで申し訳ありません、出力はPHPです。
PDOのコードはデータベースに接続したあと、

$stmt = $db->prepare('SELECT AVG(A店売上), AVG(B店売上), AVG(C店売上), AVG(C店売上), AVG(D店売上), AVG(E店売上), AVG(F店売上) FROM 売上一覧');
$stmt->execute();
$Result_ave = $stmt->fetch(PDO::FETCH_ASSOC);

foreachで$Result_aveを出力しています。
このコードでは全件の平均値になってしましまいます。

オートインクリメントでIDがあるので、日付等を指定しなくても、常に最新のIDの３，７，30件の平均値をAVGで求める事は可能でしょうか

Comments

[退会済みユーザー]

CREATE 文で表現されるテーブル定義と、サンプルデータを提示してください。

[knrdai]

追記を記入しました。後出しで申し訳ありません

[退会済みユーザー]

テーブルの定義を追記してください。

[knrdai]

何度もお手数をお掛けし、すいません。テーブルの定義とはテーブルの構造のことでしょうか？ テーブルの構造は、 ID(INT),A店売上(INT),B店売上(INT),C店売上(INT),D店売上(INT),E店売上(INT),F店売上(INT),更新日(TIMESTAMP) になっております。

[SVC34]

３日間、７日間、30日間それぞれの平均を一回のSQLで求めたいのでしょうか

[knrdai]

３、７、30日間別の関数にしようと思っているので、一つづつで構いません

Answer 1

まず、A店売上, B店売上・・・がカラムが別れているのはSQL的な
データ管理ではありません。
日付,お店,売上で管理するのが普通でしょう。

現行の管理を採用するとして、
idごとに必ず各お店の売上が絶対にうまっているのかによって
過去ナン日というロジックがかわってきます。
またid順で管理するとして連続しているのか抜け番があるかによっても違います
idの浅いデータは履歴がすくないため平均が正確にとれないのは問題ないですか？
（たとえば最初の10個目のデータは30日の平均は取れない）

Comments

[knrdai]

ご返答ありがとうございます。
データに抜けている部分や、idに抜け番はありません。また、履歴の少ないときの正確性も考慮していただかなくて、大丈夫です

[knrdai]

データベースの設計からきちんと勉強したいと思います。ありがとうございました。

Answer 2

SVC34様のご指摘の通り間違っておりましたので修正いたしました。ご指摘大変ありがとうございます。
１レコードに全店1日分の入っているレコードだとして、平均を3ラインで出力

(SELECT ' 3日売上平均' 日数, AVG(`A店売上`) A店, AVG(`B店売上`) B店, AVG(`C店売上`) C店, AVG(`D店売上`) D店, AVG(`E店売上`) E店, AVG(`F店売上`) F店
 FROM (SELECT * FROM `売上一覧` ORDER BY id DESC LIMIT 3) TB)
UNION ALL
(SELECT ' 7日売上平均' 日数, AVG(`A店売上`), AVG(`B店売上`), AVG(`C店売上`), AVG(`D店売上`), AVG(`E店売上`), AVG(`F店売上`)
 FROM (SELECT * FROM `売上一覧` ORDER BY id DESC LIMIT 7) TB)
UNION ALL
(SELECT '30日売上平均' 日数, AVG(`A店売上`), AVG(`B店売上`), AVG(`C店売上`), AVG(`D店売上`), AVG(`E店売上`), AVG(`F店売上`)
 FROM (SELECT * FROM `売上一覧` ORDER BY id DESC LIMIT 30) TB)
ORDER BY 1;

.
.
.

売上一覧のテーブルを仮想定して作成してみました。 最新3日のみですが、後はintervalを変更するのはどうでしょうか。

create table `売上一覧` (`店コード` char, `売上日` date, `売上` int);

SELECT AVG(`A店売上`), AVG(`B店売上`), AVG(`C店売上`), AVG(`D店売上`) FROM
  (
  SELECT CASE WHEN `店コード`='A' THEN `売上` ELSE NULL END `A店売上`,
         CASE WHEN `店コード`='B' THEN `売上` ELSE NULL END `B店売上`,
         CASE WHEN `店コード`='C' THEN `売上` ELSE NULL END `C店売上`,
         CASE WHEN `店コード`='D' THEN `売上` ELSE NULL END `D店売上`
  FROM `売上一覧` WHERE
  `売上日` BETWEEN SUBDATE((SELECT MAX(`売上日`) FROM `売上一覧`),3 - 1) AND (SELECT MAX(`売上日`) FROM `売上一覧`)
  ) t1
;

Comments

[A.Ichi]

上記の方法の場合、売り上げが無い店舗（店休？）の平均の分母が他と異なります。

[knrdai]

追記を記入しました。後出しで申し訳ありませんが、常に最新のIDから３，７，30件の平均値をAVGで求める事は可能でしょうか

[A.Ichi]

確認ですが、レコードは各店の1日の売上が作成されていて、そのIDが連番で古い順から振られているテーブルが有ると言うことでしょうか？

[knrdai]

ひとつのレコードに全店舗１日分のデータが入ってます

[A.Ichi]

ひとつのレコードに入っていたのですね。勘違いをしておりました。3行で出力する様にしました。

[knrdai]

拙い説明で申し訳ありませんでした。SQLもPHPももっと勉強します

Answer 3

日付で絞る場合はこんな感じです。

'SELECT AVG(A店売上), AVG(B店売上), AVG(C店売上), AVG(D店売上) FROM 売上一覧 WHERE 日付 > 20170826 AND 日付 < 20170827'

テーブル定義とサンプルレコードを記載いただければもう少し詳細な回答が出来るかと思います。

Comments

[knrdai]

ご返答ありがとうございます。
追記を記入しました。後出しで申し訳ありませんが、常に最新のIDから３，７，30件の平均値をAVGで求める事は可能でしょうか

[wuzzy]

```
'SELECT AVG(A店売上), AVG(B店売上), AVG(C店売上), AVG(D店売上) FROM 売上一覧 ORDER BY ID DESC LIMIT 3'
```
日付を取り払っていますが、このような感じでしょうか。

[knrdai]

ご返答ありがとうございます。
ORDER BY ID DESC LIMIT 3
が有効になっていないようで、全件の平均値になってしまいます。
PDOを使うと金額が文字列として返ってきてしまうので、SQLでなんとかしようとしていますが、普通はPHP側で計算するものでしょうか？

Answer 4

これまで出てきた要件からすると、こうでしょうか

SELECT
    AVG(`A店売上`)
    ,AVG(`B店売上`)
    ,AVG(`C店売上`)
    ,AVG(`D店売上`)
FROM (
    SELECT
        `A店売上`
        ,`B店売上`
        ,`C店売上`
        ,`D店売上`
    FROM
        `売上一覧`
    ORDER BY
        ID DESC
    LIMIT 3
) AS tmp

Comments

[knrdai]

やりたいことズバリのSQL文でした。ベストアンサーにさせていただきます。ありがとうございました。副問合せをもっと勉強したいと思います

[SVC34]

他の方の回答ではAVGによる集計とLIMITを同時に使用しているのですが、LIMITにより件数が制限されるのは集約関数等SELECT句の評価を行った後であるため、結局全レコードが集計対象になってしまいます。そこで、一度副問合せで集計せずに最新のnレコードを取得し、外側のクエリで集計するようにしています。

SELECT文の評価順序の話
http://qiita.com/suzukito/items/edcd00e680186f2930a8

[knrdai]

ありがとうございます。参考にさせていただきます。SQLはプログラミング言語に依存することなく重要なのできちんと勉強しようと思います。