0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

MySQL / DATE_FORMAT関数で同一日付をカウント、順位付けする

Posted at

#まえがき
MySQL-DATE_FORMAT関数の挙動確認です。

#前提条件

MySQLで簡単にランダムなテストデータを作成する方法
こちらの記事にランダムデータを作成する方法が載っていましたので、同様の手順でテーブルを作成しておきます。

20210801024646_ChangeUsers.php
# id, name, description, price, created_at
1, 商品1, 8a44ac7a37f7bd69a7db99d3fe937b, 8595, 2014-04-18 09:12:02
2, 商品2, 493b3579d0986774aed5dab8dd8ca4, 5489, 2014-02-28 23:28:33
3, 商品3, a022f42410b9bd6ec080c3e5112be3, 6452, 2014-03-28 10:54:58
4, 商品4, 2be6c9822811b7586c46e86417e466, 8356, 2014-04-29 18:44:42
6, 商品6, cf2151b45afa9c148f2260df480617, 3202, 2014-05-25 01:16:46
7, 商品7, 452fcde3e7130a92e51015240d8af2, 1319, 2014-01-16 00:35:53

こんな風にあっという間に数十万レコードのダミーデータが作成されます。

#DATE_FORMAT関数

DATE_FORMAT 関数を使うと引数に指定した日付を表す値を指定のフォーマットで整形した文字列を取得します。また TIME_FORMAT 関数を使うと時刻を表す値を指定のフォーマットで整形した文字列を取得できます。
DATE_FORMAT関数 / TIME_FORMAT関数 (日付または時刻を指定したフォーマットで整形する)

PHPでいうところのdate関数やstrtotime関数のような事が可能という事ですね。

・日時情報の変換

日付 - スラッシュ区切り
select created_at as bef,date_format(created_at, '%Y/%m/%d') as aft  from item 
# bef, aft
'2014-04-18 09:12:02', '2014/04/18'
'2014-02-28 23:28:33', '2014/02/28'
'2014-03-28 10:54:58', '2014/03/28'
'2014-04-29 18:44:42', '2014/04/29'
'2014-05-25 01:16:46', '2014/05/25'

日付 - 英語月名 / 英字曜日 / AM・PM表記

select created_at as bef,date_format(created_at, '%Y/%m/%d') as aft  from item 
# bef, aft
'2014-04-18 09:12:02', 'Apr-18(Friday) AM 09'
'2014-02-28 23:28:33', 'Feb-28(Friday) PM 11'
'2014-03-28 10:54:58', 'Mar-28(Friday) AM 10'
'2014-04-29 18:44:42', 'Apr-29(Tuesday) PM 06'
'2014-05-25 01:16:46', 'May-25(Sunday) AM 01'

#こんな事も可能です。

SELECT文でgroup関数と併用する事で指定フォーマットの日付による絞り込みを行う事ができます。

created_atカラムの値を日付までの値でフォーマットし、
日付が等しいレコード数を集計する。

select count(*) as C, date_format(created_at, "%Y-%m-%d") as D from item group by D order by  C DESC limit 10;
# C, D
'8', '2014-04-24'
'8', '2014-01-25'
'7', '2014-05-25'
'7', '2014-01-29'
'7', '2014-02-16'
'7', '2014-05-17'
'6', '2014-03-12'
'6', '2014-06-14'
'6', '2014-03-13'
'6', '2014-05-07'

created_atカラムの値を月までの値でフォーマットし、
月内のレコード数を集計、priceカラムの合計値を返す。

select CONCAT(CAST(SUM(price) as char(20)),' yen') as P,count(*) as C, date_format(created_at, "%Y-%m") as D from item group by D order by  P DESC;
# P, C, D
'491773 yen', '94', '2014-06'
'479018 yen', '97', '2014-01'
'453588 yen', '82', '2014-04'
'424187 yen', '79', '2014-03'
'418671 yen', '86', '2014-05'
'368871 yen', '74', '2014-02'

#あとがき

調べている時はgroup関数と何が違うのかあまりわかりませんでしたが、
フォーマットできるだけで色々できて非常に便利だな~と思いました。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?