MySQL の日付形式で統計を集計する方法は何ですか?

はじめに

時刻と日付は一般的に使用されるグループ化条件であり、実際の開発ではデータの統計を容易にするためによく使用されます。 MySQL では、日付書式設定関数を使用して日付を指定された形式の文字列に変換し、必要な時間粒度に従ってグループ統計を実行できます。

準備

始める前に、テスト データ テーブルを準備し、その後の実験やテストを容易にするためにデータを挿入する必要があります。

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `test` (`name`, `created_at`) VALUES
('test1', '2022-01-01 00:00:00'),
('test2', '2022-01-02 00:00:00'),
('test3', '2022-01-02 12:00:00'),
('test4', '2022-01-03 00:00:00'),
('test5', '2022-01-07 00:00:00'),
('test6', '2022-01-08 00:00:00'),
('test7', '2022-01-09 00:00:00'),
('test8', '2022-01-10 00:00:00'),
('test9', '2022-01-14 00:00:00'),
('test10', '2022-01-15 00:00:00'),
('test11', '2022-01-16 00:00:00'),
('test12', '2022-01-17 00:00:00'),
('test13', '2022-02-01 00:00:00'),
('test14', '2022-02-02 00:00:00'),
('test15', '2022-02-03 00:00:00'),
('test16', '2022-03-01 00:00:00'),
('test17', '2022-03-02 00:00:00'),
('test18', '2022-03-03 00:00:00'),
('test19', '2022-04-01 00:00:00'),
('test20', '2022-04-02 00:00:00'),
('test21', '2022-04-03 00:00:00'),
('test22', '2022-05-01 00:00:00'),
('test23', '2022-05-02 00:00:00'),
('test24', '2022-05-03 00:00:00');

ここでは、id、name、created_at の 3 つのフィールドを含む、test という名前のテスト テーブルを作成します。このうち、created_atは作成時刻を記録する日時型フィールドを表します。 2022 年 1 月から 5 月までのデータを含むいくつかのテスト データを挿入しました。

実装原則

日付に基づいて統計をグループ化する必要がある場合、MySQL には YEAR などの多くの組み込み日付関数が用意されています。 ()、MONTH()、WEEK()、DAY()、HOUR() など。これらの関数はデータを日付ごとにグループ化し、対応する数量をカウントします。

この要件では、日付ごとにグループ化し、7 日、4 週間、3 か月のデータを計算する必要があります。したがって、日付書式設定と日付関数を組み合わせて使用​​する必要があります。

まず、日付を対応する形式にフォーマットする必要があります。これを行うには、DATE_FORMAT() 関数を使用します。この関数は、日付と形式文字列の 2 つのパラメーターを受け取ります。日付は、以下に示すように「yyyy-MM-dd」の形式で指定できます。例:

SELECT DATE_FORMAT(date_column, '%Y-%m-%d') AS formatted_date FROM table_name;

実際の操作

日ごとの統計

次に、グループ化する必要があります。日付と数量を数えます。これは、GROUP BY 句と対応する日付関数を使用して実現できます。たとえば、次のように、日付のグループ化に従って 1 日の数量をカウントできます:

SELECT DATE_FORMAT(date_column, '%Y-%m-%d') AS formatted_date, COUNT(*) AS count FROM table_name GROUP BY formatted_date;

Count by week

SELECT DATE_FORMAT(date_column, '%x-%v') AS formatted_week, COUNT(*) AS count FROM table_name GROUP BY formatted_week;

または

SELECT CONCAT(YEAR(date_column), '-', WEEK(date_column)) AS formatted_week, COUNT(*) AS count FROM table_name GROUP BY formatted_week;`

Count by month

SELECT DATE_FORMAT(date_column, '%Y-%m-%d') AS formatted_month, COUNT(*) AS count FROM table_name GROUP BY formatted_month;

or

SELECT CONCAT(YEAR(date_column), '-', MONTH(date_column)) AS formatted_month, COUNT(*) AS count FROM table_name GROUP BY formatted_month;

以上がMySQL の日付形式で統計を集計する方法は何ですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。