平均の平均を求めたい。

Question

実現したいこと

以下のようなテーブルがあります。

DROP TABLE IF EXISTS `logs`;
CREATE TABLE `logs` (
  `task_id` int(11) NOT NULL,
  `mac` varchar(17) NOT NULL,
  `collection_time` timestamp NOT NULL DEFAULT current_timestamp(),
  `mv` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`mv`)),
  `response` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`task_id`,`mac`,`collection_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `logs` VALUES
(1,'a','2025-02-20 00:00:00','{"20":1}',1),
(1,'b','2025-02-20 00:00:00','{"20":2,  "25":3}',  1),
(1,'c','2025-02-20 00:00:00','{"20":4,  "25":5,    "30":6}',  1),
(1,'d','2025-02-20 00:00:00','{"20":7,  "25":8,    "30":9,    "35":10}', 1),
(1,'a','2025-02-20 00:01:00','{"20":11}',1),
(1,'b','2025-02-20 00:01:00','{"20":12, "25":13}', 1),
(1,'c','2025-02-20 00:01:00','{"20":14, "25":15,   "30":16}', 1),
(1,'d','2025-02-20 00:01:00','{"20":17, "25":18,   "30":19,   "35":20}', 1);

SELECT collection_time, mac, mv FROM logs ORDER BY collection_time, mac;

+---------------------+-----+------------------------------------------+
| collection_time | mac | mv |
+---------------------+-----+------------------------------------------+
| 2025-02-20 00:00:00 | a | {"20":1} |
| 2025-02-20 00:00:00 | b | {"20":2, "25":3} |
| 2025-02-20 00:00:00 | c | {"20":4, "25":5, "30":6} |
| 2025-02-20 00:00:00 | d | {"20":7, "25":8, "30":9, "35":10} |
| 2025-02-20 00:01:00 | a | {"20":11} |
| 2025-02-20 00:01:00 | b | {"20":12, "25":13} |
| 2025-02-20 00:01:00 | c | {"20":14, "25":15, "30":16} |
| 2025-02-20 00:01:00 | d | {"20":17, "25":18, "30":19, "35":20} |
+---------------------+-----+------------------------------------------+

このテーブルに対してクエリーを行い、以下のような結果を得たいと思います。

SELECT 
    collection_time,
    LEAST(
    MIN(CASE WHEN JSON_CONTAINS_PATH(mv, 'one', '$."20"') = 0 THEN 99999
        ELSE CAST(JSON_UNQUOTE(JSON_EXTRACT(mv, '$."20"')) AS DECIMAL(10, 2)) END)
,   MIN(CASE WHEN JSON_CONTAINS_PATH(mv, 'one', '$."25"') = 0 THEN 99999
        ELSE CAST(JSON_UNQUOTE(JSON_EXTRACT(mv, '$."25"')) AS DECIMAL(10, 2)) END)
,   MIN(CASE WHEN JSON_CONTAINS_PATH(mv, 'one', '$."30"') = 0 THEN 99999
        ELSE CAST(JSON_UNQUOTE(JSON_EXTRACT(mv, '$."30"')) AS DECIMAL(10, 2)) END)
,   MIN(CASE WHEN JSON_CONTAINS_PATH(mv, 'one', '$."35"') = 0 THEN 99999
        ELSE CAST(JSON_UNQUOTE(JSON_EXTRACT(mv, '$."35"')) AS DECIMAL(10, 2)) END)
    ) as min,
    GREATEST(
    MAX(CASE WHEN JSON_CONTAINS_PATH(mv, 'one', '$."20"') = 0 THEN -99999
        ELSE CAST(JSON_UNQUOTE(JSON_EXTRACT(mv, '$."20"')) AS DECIMAL(10, 2)) END)
,   MAX(CASE WHEN JSON_CONTAINS_PATH(mv, 'one', '$."25"') = 0 THEN -99999
        ELSE CAST(JSON_UNQUOTE(JSON_EXTRACT(mv, '$."25"')) AS DECIMAL(10, 2)) END)
,   MAX(CASE WHEN JSON_CONTAINS_PATH(mv, 'one', '$."30"') = 0 THEN -99999
        ELSE CAST(JSON_UNQUOTE(JSON_EXTRACT(mv, '$."30"')) AS DECIMAL(10, 2)) END)
,   MAX(CASE WHEN JSON_CONTAINS_PATH(mv, 'one', '$."35"') = 0 THEN -99999
        ELSE CAST(JSON_UNQUOTE(JSON_EXTRACT(mv, '$."35"')) AS DECIMAL(10, 2)) END)
    ) AS max,
    '???' AS avg
FROM logs
GROUP BY collection_time;

+---------------------+-------+-------+-----+
| collection_time | min | max | avg |
+---------------------+-------+-------+-----+
| 2025-02-20 00:00:00 | 1.00 | 10.00 | ??? |
| 2025-02-20 00:01:00 | 11.00 | 20.00 | ??? |
+---------------------+-------+-------+-----+

この、avg の取得方法についてご教示ください。
avg の計算方法は以下の通りです。

SELECT 
    collection_time, mac, 
    CASE WHEN JSON_CONTAINS_PATH(mv, 'one', '$.20') = 1
         THEN JSON_EXTRACT(mv, '$.20') ELSE '' END +
    CASE WHEN JSON_CONTAINS_PATH(mv, 'one', '$.25') = 1
         THEN JSON_EXTRACT(mv, '$.25') ELSE '' END +
    CASE WHEN JSON_CONTAINS_PATH(mv, 'one', '$.30') = 1
         THEN JSON_EXTRACT(mv, '$.30') ELSE '' END +
    CASE WHEN JSON_CONTAINS_PATH(mv, 'one', '$.35') = 1
         THEN JSON_EXTRACT(mv, '$.35') ELSE '' END  AS sum,
    (JSON_CONTAINS_PATH(mv, 'one', '$.20')) +
    (JSON_CONTAINS_PATH(mv, 'one', '$.25')) +
    (JSON_CONTAINS_PATH(mv, 'one', '$.30')) +
    (JSON_CONTAINS_PATH(mv, 'one', '$.35'))         AS count,
    (
    CASE WHEN JSON_CONTAINS_PATH(mv, 'one', '$.20') = 1
         THEN JSON_EXTRACT(mv, '$.20') ELSE '' END +
    CASE WHEN JSON_CONTAINS_PATH(mv, 'one', '$.25') = 1
         THEN JSON_EXTRACT(mv, '$.25') ELSE '' END +
    CASE WHEN JSON_CONTAINS_PATH(mv, 'one', '$.30') = 1
         THEN JSON_EXTRACT(mv, '$.30') ELSE '' END +
    CASE WHEN JSON_CONTAINS_PATH(mv, 'one', '$.35') = 1
         THEN JSON_EXTRACT(mv, '$.35') ELSE '' END
    ) / (
    (JSON_CONTAINS_PATH(mv, 'one', '$.20')) +
    (JSON_CONTAINS_PATH(mv, 'one', '$.25')) +
    (JSON_CONTAINS_PATH(mv, 'one', '$.30')) +
    (JSON_CONTAINS_PATH(mv, 'one', '$.35'))
    )  AS avg
FROM logs
ORDER BY collection_time, mac
;

+---------------------+-----+------+-------+------+
| collection_time | mac | sum | count | avg |
+---------------------+-----+------+-------+------+
| 2025-02-20 00:00:00 | a | 1 | 1 | 1 |
| 2025-02-20 00:00:00 | b | 5 | 2 | 2.5 |
| 2025-02-20 00:00:00 | c | 15 | 3 | 5 |
| 2025-02-20 00:00:00 | d | 34 | 4 | 8.5 |
| 2025-02-20 00:01:00 | a | 11 | 1 | 11 |
| 2025-02-20 00:01:00 | b | 25 | 2 | 12.5 |
| 2025-02-20 00:01:00 | c | 45 | 3 | 15 |
| 2025-02-20 00:01:00 | d | 74 | 4 | 18.5 |
+---------------------+-----+------+-------+------+

１つの collection_time に対して、avg が mac の個数分作られています。
で、最終的に求めたい avg は、 (1 + 2.5 + 5 + 8.5 ) / 4 = 4.25 と (11 + 12.5 + 15 + 17.5) / 4 = 14 となります。

以上、よろしくお願いします。

発生している問題・分からないこと

平均の平均を求める方法を知りたい。

該当のソースコード

特になし

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

同様の質問を見つけることができなかった。

補足

テーブルはかなり省略して書いてありますが、実際にはJSONフィールドにキーが200以上あります。
レコード数が非常に多いため（JSONにしても、論理的には1億行オーバー）、このような構成になっています。データベースはMariaDB-10.11.9です。

Comments

[yambejp]

レコード数が多いのであれば集計に不利なJSON型での管理はやめたほうがよいかと
１億行となると更に特殊なチューニングが必要でしょう

[hotta]

時系列で取得するデータ自体が割と頻繁に変わるので、スキーマの変更が不要なこの形に落ち着いています。同じシステムの別テーブルでは10億行オーバーもありますが、パーティショニング等でどうにか動いています。

[yambejp]

基本的にログデータは検索さえできればいいのでJSON型でも構いませんが集計には向いていません。
集計を前提とするなら定期的に必要な集計データした状態で別途専用のテーブルに流し込んでおき、リアルタイムでの集計は諦めたほうがよいでしょう。（数万件であればリアルタイム処理も可能でしょうけど）

[utm.]

計算方法は以下を参考にしました。
> １つの collection_time に対して、avg が mac の個数分作られています。
> で、最終的に求めたい avg は、 (1 + 2.5 + 5 + 8.5 ) / 4 = 4.25 と (11 + 12.5 + 15 + 17.5) / 4 = 14 となります。

以下の方法ではだめという質問趣旨ですか？

DROP TABLE IF EXISTS `logs`;
CREATE TABLE `logs` (
`task_id` int(11) NOT NULL,
`mac` varchar(17) NOT NULL,
`collection_time` timestamp NOT NULL DEFAULT current_timestamp(),
`mv` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`mv`)),
`response` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`task_id`,`mac`,`collection_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `logs` VALUES
(1,'a','2025-02-20 00:00:00','{"20":1}',1),
(1,'b','2025-02-20 00:00:00','{"20":2, "25":3}', 1),
(1,'c','2025-02-20 00:00:00','{"20":4, "25":5, "30":6}', 1),
(1,'d','2025-02-20 00:00:00','{"20":7, "25":8, "30":9, "35":10}', 1),
(1,'a','2025-02-20 00:01:00','{"20":11}',1),
(1,'b','2025-02-20 00:01:00','{"20":12, "25":13}', 1),
(1,'c','2025-02-20 00:01:00','{"20":14, "25":15, "30":16}', 1),
(1,'d','2025-02-20 00:01:00','{"20":17, "25":18, "30":19, "35":20}', 1);

SELECT SUM(subquery.avg) / COUNT(subquery.avg) AS column_averages,
SUM(subquery.avg) as s,
COUNT(subquery.avg) as c
FROM (
SELECT
collection_time, mac,
CASE WHEN JSON_CONTAINS_PATH(mv, 'one', '$.20') = 1
THEN JSON_EXTRACT(mv, '$.20') ELSE '' END +
CASE WHEN JSON_CONTAINS_PATH(mv, 'one', '$.25') = 1
THEN JSON_EXTRACT(mv, '$.25') ELSE '' END +
CASE WHEN JSON_CONTAINS_PATH(mv, 'one', '$.30') = 1
THEN JSON_EXTRACT(mv, '$.30') ELSE '' END +
CASE WHEN JSON_CONTAINS_PATH(mv, 'one', '$.35') = 1
THEN JSON_EXTRACT(mv, '$.35') ELSE '' END AS sum,
(JSON_CONTAINS_PATH(mv, 'one', '$.20')) +
(JSON_CONTAINS_PATH(mv, 'one', '$.25')) +
(JSON_CONTAINS_PATH(mv, 'one', '$.30')) +
(JSON_CONTAINS_PATH(mv, 'one', '$.35')) AS count,
(
CASE WHEN JSON_CONTAINS_PATH(mv, 'one', '$.20') = 1
THEN JSON_EXTRACT(mv, '$.20') ELSE '' END +
CASE WHEN JSON_CONTAINS_PATH(mv, 'one', '$.25') = 1
THEN JSON_EXTRACT(mv, '$.25') ELSE '' END +
CASE WHEN JSON_CONTAINS_PATH(mv, 'one', '$.30') = 1
THEN JSON_EXTRACT(mv, '$.30') ELSE '' END +
CASE WHEN JSON_CONTAINS_PATH(mv, 'one', '$.35') = 1
THEN JSON_EXTRACT(mv, '$.35') ELSE '' END
) / (
(JSON_CONTAINS_PATH(mv, 'one', '$.20')) +
(JSON_CONTAINS_PATH(mv, 'one', '$.25')) +
(JSON_CONTAINS_PATH(mv, 'one', '$.30')) +
(JSON_CONTAINS_PATH(mv, 'one', '$.35'))
) AS avg
FROM logs
ORDER BY collection_time, mac
) subquery
GROUP BY subquery.collection_time
;

[hotta]

はい、これで求めたい avg は合っているようです。
最終的に求めたい出力は、以下となります。
+---------------------+-------+-------+-----+
| collection_time | min | max | avg |
+---------------------+-------+-------+-----+
| 2025-02-20 00:00:00 | 1.00 | 10.00 | 4.25 |
| 2025-02-20 00:01:00 | 11.00 | 20.00 | 14.25 |
+---------------------+-------+-------+-----+

[yambejp]

想定するavgが私の解と違うようですが、大丈夫でしょうか？
2025-02-20 00:00:00は1から10までの平均で5.5
2025-02-20 00:01:00は11から20までの平均で15.5

[yambejp]

よく読み返してみました、平均値を平均するんですね。それってなにか意味があるのでしょうか？
（一応調整版もあげてあります）

[utm.]

丸投げかい

Answer 1

一応こんな感じで

select collection_time,
min(value) as min,
max(value) as max,
avg(value) as avg
from (
select collection_time,value
 from
(
select 
collection_time,
replace(group_concat(JSON_EXTRACT(mv,'$.*')),'],[',',') as val
from logs
group by collection_time
) as t1,JSON_TABLE(val, '$[*]' COLUMNS (value INT PATH '$')) AS j
) as t2
group by collection_time

調整版

各データごとに平均を出して、そのデータを平均する

select collection_time,
min(min) as min,
max(max) as max,
avg(avg) as avg
from(
select collection_time,mac,
min(value) as min,
max(value) as max,
avg(value) as avg
from (
select collection_time,mac,value
 from
(
select 
collection_time,mac,
replace(group_concat(JSON_EXTRACT(mv,'$.*')),'],[',',') as val
from logs
group by collection_time,mac
) as t1,JSON_TABLE(val, '$[*]' COLUMNS (value INT PATH '$')) AS j
) as t2
group by collection_time,mac
) as t3
group by collection_time

Comments

[hotta]

調整版の方をいただきました。ありがとうございました。
平均の平均を取る理由は、、、ちょっと説明が難しいです。