UI設計したり、ウェブシステムを開発したりします。　

HOME
WORKS
SQL-BLOG
THEME-BLOG
POST HISTORY
CONTACT
MAIL
Twitter
Instagram
Facebook

SQL見るだけクエリ#019 ランキングに順位番号を取得する簡単な方法（FIND_IN_SET）応用編2

記入日：2023-01-27　編集日：2023-01-27

SQL見るだけクエリ。今回は、
以前書いた、商品別に日ごとの売上額を集計CASE文を使ってクロス集計したSQLと、
ここ数回の、FIND_IN_SETを使って順位番号をつけるSQLをミックスしたものを書いてみたいと思います。

以下のsql_orderテーブルには、販売商品毎のレコードが入っております。

テーブル名：sql_order

                    
idorder_idnameitem_idpricequantitysizedatetimestatus
11伊藤 明日香sku-000130001S2022-12-16 07:00:001
21伊藤 明日香sku-000225003S2022-12-16 07:00:001
31伊藤 明日香sku-000390001S2022-12-16 07:00:001
42原田 和美sku-000130001L2022-12-16 10:00:001
52原田 和美sku-000225001L2022-12-16 10:00:001
62原田 和美sku-000390002L2022-12-16 10:00:001
73高橋 由美子sku-000130001M2022-12-16 18:00:001
83高橋 由美子sku-000225005S2022-12-16 18:00:001
93高橋 由美子sku-000390001M2022-12-16 18:00:001
104田中 明日香sku-000130001S2022-12-16 22:00:001
114田中 明日香sku-000225001S2022-12-16 22:00:001
124田中 明日香sku-000390002S2022-12-16 22:00:001
135小林 美佐子sku-000130001L2022-12-17 10:00:001
145小林 美佐子sku-000225001L2022-12-17 10:00:001
155小林 美佐子sku-000390002L2022-12-17 10:00:001
166渡辺 雄一sku-000130001S2022-12-18 08:00:001
176渡辺 雄一sku-000225001S2022-12-18 08:00:001
186渡辺 雄一sku-000390001S2022-12-18 08:00:001
197佐藤 智子sku-0001300012M2022-12-18 15:00:001
207佐藤 智子sku-000225001S2022-12-18 15:00:001
217佐藤 智子sku-000390001M2022-12-18 15:00:001
228坂本 純子sku-000130001S2022-12-19 20:00:001
238坂本 純子sku-000225001S2022-12-19 20:00:001
248坂本 純子sku-000390003S2022-12-19 20:00:001
259加藤 和子sku-000130001L2022-12-20 10:00:001
269加藤 和子sku-000225008L2022-12-20 10:00:001
279加藤 和子sku-000390001L2022-12-20 10:00:001
2810山口 晴子sku-000130001M2022-12-20 19:00:001
2910山口 晴子sku-000225001L2022-12-20 19:00:001
3010山口 晴子sku-000390003L2022-12-20 19:00:001

この記事を読むと（約5分）
SQLを使って、商品毎の日別の売上を集計しつつ、商品毎に日別のランキングを取得する方法がわかります。
今回は、直にSQL文を書くとかなり長くなってしまいそうでしたので、SQLの一部をプログラムで事前に生成する書き方にしました。

今回も使う関数的には、前回同様、FIND_IN_SETやGROUP_CONCATです。 これらと副問合せ（サブクエリ）、CASE文などを組み合わせて、ランキングの値を導きます。

少し複雑な内容になりますのでなんとなく雰囲気が伝われば良いかなと思っております。

try {
    include_once '../model/class.php';

    $query_source = NULL;
    $items = array('sku-0001', 'sku-0002', 'sku-0003');

    foreach($items as $i => $item_id) {
        $query_source .= '
        SUM(CASE WHEN item_id = "' . $item_id . '" 
            THEN price*quantity ELSE 0 END) AS "payment_' . ($i + 1) . '" ,
        FIND_IN_SET(SUM(CASE WHEN item_id = "' . $item_id . '" 
            THEN price*quantity ELSE 0 END),(
                SELECT GROUP_CONCAT(payment_' . ($i + 1) . ') 
                FROM(
                    SELECT DATE(datetime) AS date ,
                    SUM(CASE WHEN item_id = "' . $item_id . '" 
                        THEN price*quantity ELSE 0 END) AS payment_' . ($i + 1) . ' 
                    FROM sql_order 
                    GROUP BY date 
                    ORDER BY payment_' . ($i + 1) . ' DESC
                ) AS payment_' . ($i + 1) . '_array
        )) AS rank_' . ($i + 1) . ',
        ';
    }

    // データベース接続
    $database = new Database();
    $db = $database->connect();

    // SQLを準備し、パラメータをバインドする
    $query = 'SELECT  DATE(datetime) AS date ,
     ' . $query_source . ' 
    SUM(price*quantity) AS "total" 
    FROM sql_order 
    GROUP BY date';
    $stmt = $db->prepare($query);

    // 実行する
    $stmt->execute();

    // 結果を取得する
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    print_r($result);

    // データベースから切断する
    $database->disconnect();

} catch(PDOException $e) {
    echo $e->getMessage();
}

これを実行すると、二次元連想配列で、取得することができます。

            Array
(
    [0] => Array
        (
            [date] => 2022-12-16
            [payment_1] => 12000
            [rank_1] => 2
            [payment_2] => 25000
            [rank_2] => 1
            [payment_3] => 54000
            [rank_3] => 1
            [total] => 91000
        )

    [1] => Array
        (
            [date] => 2022-12-17
            [payment_1] => 3000
            [rank_1] => 4
            [payment_2] => 2500
            [rank_2] => 4
            [payment_3] => 18000
            [rank_3] => 4
            [total] => 23500
        )

    [2] => Array
        (
            [date] => 2022-12-18
            [payment_1] => 39000
            [rank_1] => 1
            [payment_2] => 5000
            [rank_2] => 3
            [payment_3] => 18000
            [rank_3] => 4
            [total] => 62000
        )

    [3] => Array
        (
            [date] => 2022-12-19
            [payment_1] => 3000
            [rank_1] => 4
            [payment_2] => 2500
            [rank_2] => 4
            [payment_3] => 27000
            [rank_3] => 3
            [total] => 32500
        )

    [4] => Array
        (
            [date] => 2022-12-20
            [payment_1] => 6000
            [rank_1] => 3
            [payment_2] => 22500
            [rank_2] => 2
            [payment_3] => 36000
            [rank_3] => 2
            [total] => 64500
        )

)

売上一覧（日付x商品別）
販売額クロス集計表 ランキング付き

日付 sku-0001 順 sku-0002 順 sku-0003 順 合計数
2022-12-16
12,000
2
25,000
1
54,000
1
91,000
2022-12-17
3,000
4
2,500
4
18,000
4
23,500
2022-12-18
39,000
1
5,000
3
18,000
4
62,000
2022-12-19
3,000
4
2,500
4
27,000
3
32,500
2022-12-20
6,000
3
22,500
2
36,000
2
64,500

SQLの使いどころ

サイトに大量の情報を掲載することは、ユーザーにとってとても便利なことです。しかし、情報が多すぎると、目的の情報を見つけるのが困難になります。そのため、SQLを使用することで、情報を効率的に管理することができます。

SQLを使用することで、データベース内の情報を検索したり、フィルタリングしたりすることができます。これにより、ユーザーは目的の情報をスムーズかつ簡単に見つけることができます。

また、SQLを使用することで、データを集計したり、統合したりすることもできます。これにより、ユーザーはさまざまな視点から情報を分析することができます。つまり、サイトに多くの情報を掲載することが重要である一方で、その情報を効率的かつ簡単に管理することが求められる場合には、SQLが不可欠であるといえます。

KK

機械工学を専攻。工業デザイナーとして、国内及び海外の自動車・搬送ラインの設計などに従事した後、2003年にウェブシステム会社を設立。UI設計やウェブシステムの開発、DX化のディレクションなどを行っています。