データベースからクエリでテキストデータを時間準拠で分割する方法について

Question

<やりたいこと>
データベースからのmeeting_noカラムの時間の全長を計算した上で、
ミーティングごとに区分する数に基づいてテキストファイルを分割して書き出したいと考えております。

<想定されるデータベース>

|meeting_no|start_second|text|
|1|0.0|こんにちは
|1|2.2|おはようございます
|1|3.3|今日はいい天気ですね
|1|4.4|ご飯を食べました
|1|5.1|朝は気温が低いため、外出する際は防寒が必要であると思われます
|1|8.0|ご飯を食べました
|1|9.0|昼は気温が高いため、洗濯物日和です
|1|10.0|眠いです
|1|11.0|おやつの時間です
|1|12.0|おやつを食べました
|2|・・
|2|・・
：

<考えていること>
・meeting_idごとにgroupby、split_timeはmeeting_id毎に異なる
・time_length = start_secondの最大値をもとに12を自動算出
・split_num = splitしたい任意の数、仮に5とする
・split_time = 2.4を自動算出
・start_secondが0.0は一番小さいグループ割り当て

<想定される集計結果>

|meeting_no|split_id|text|
|1|1|こんにちは\nおはようございます
|1|2|今日はいい天気ですね\nご飯を食べました
|1|3|朝は気温が低いため、外出する際は防寒が必要であると思われます
|1|4|ご飯を食べました\n昼は気温が高いため、洗濯物日和です
|1|5|眠いです\nおやつの時間です\nおやつを食べました

上記のように、time_lengthを求めた上でテキストデータを分割(結合)したいと考えております。
中間テーブルとしてwith句でsplit_num、split_time、time_lengthを計算し、
start_secondのカラムに対してmeeting_id毎にsplit_timeに当てればよいと思いますが、
いまいち想定通りの結果を出力できず、textをうまく結合することができず困っております。

お手数を掛けしますが、上記をうまく実装する方法についてお知恵を拝借できますと幸いです。
何卒よろしくお願い申し上げます。

Comments

[m.ts10806]

「データベーススペシャリスト」はあくまで情報処理技術者試験の一つです。本件とはどう関係するのでしょうか？

[m.ts10806]

「MySQL」なら「BigQuery」は関係ないようにも思います。バージョンはいくつでしょうか。
あと、テーブル定義も必要ですし、自身が組んでみたコードも必要です。

[yambejp]

プロシージャでやる感じでは？
group byしてgroup_concatでもある程度までできそうですが・・・
0.0と2.2のデータは1じゃなくて0ですよね？
逆にこれらを1とするなら12.0は単独で6になりませんか？

Answer 1

• 同じmeeting_noデータをsplit_num種類に分類したい
• 分類方法: meeting_no=1の場合、start_secondが0.0以上2.4未満であればsplit_id=1、start_secondが2.4以上4.8未満であればsplit_id=2 ... というように分類する
• 同じsplit_idに分類された行のtextはstart_secondの昇順に並べて\nで結合する。

やりたいことは↑であっているでしょうか? であれば以下のようなSQLになります (split_num=5をハードコードしています)。ただSQLが複雑になりすぎるので、アプリケーション側で実装したほうがよいかとは思います。

WITH 
s AS (
  SELECT
    meeting_no,
    MAX(start_second) / 5 AS divisor
  FROM mtg
  GROUP BY meeting_no 
),
t AS (
  SELECT
    m.meeting_no,
    m.start_second,
    m.text,
    TRUNCATE(m.start_second / s.divisor, 0) AS split_id
  FROM mtg AS m
  JOIN s ON m.meeting_no = s.meeting_no
),
u AS (
  SELECT
    meeting_no,
    start_second,
    text,
    IF(split_id < 5, split_id, 5 - 1) + 1 AS split_id
  FROM t
)
SELECT
  meeting_no,
  split_id,
  GROUP_CONCAT(text ORDER BY start_second SEPARATOR '\n') AS text
FROM u
GROUP BY meeting_no, split_id;