SQLで３つのカラムをあわせてソートしたい

Question

###前提・実現したいこと
以下２つの条件で「threadのリスト」を取得しています。

➀彼が投稿したthread
➁または彼がfollow|favoriteしたthread

こうして取得された「threadのリスト」には
・his_post_date（彼が投稿した日）
・follow_date（彼がfollowした日）
・favorite_date（彼がfavoriteした日）
と３つの日付があるのですが、これらをあわせて昇順でソートしたいと思っています。

###CREATE TABLE と INSERT
対象のテーブルです。３つあります。

/*
■テーブルの構造
掲示板サイトのテーブルで、以下3つがあります。
・actions_table  : アクション
・contents_table : 日本ユーザー以外の全て
・users _table   : 日本ユーザー
*/

# actions_table
# アクション
CREATE TABLE actions_table
    (action_id int, action_name text, action_date text, actor_id int, target_id int, target_type text, target_country text)
;
INSERT INTO actions_table
    (action_id, action_name, action_date, actor_id, target_id, target_type, target_country)
VALUES
    (1,  'follow',   '2020-01-00 00:00:00', 1, 2,  'user',    'jp'),
    (2,  'follow',   '2020-02-00 00:00:00', 1, 2,  'thread',  'jp'),
    (3,  'favorite', '2020-03-00 00:00:00', 1, 3,  'thread',  'jp'),
    (4,  'favorite', '2020-04-00 00:00:00', 2, 3,  'thread',  'jp'),
    (5,  'block',    '2020-05-00 00:00:00', 1, 3,  'user',    'jp')
;

# contents_table
# 日本ユーザー以外の全て
CREATE TABLE contents_table
    (content_id int, author_id int, path text, content_date text, parent_thread_id int, content_country text, parent_content_id int, content_type text, content_text text, attach_id int, attach_type text, attach_country text)
;  
INSERT INTO contents_table
    (content_id, author_id, content_date, content_country, content_type, content_text )
VALUES
    (1, 0, '2020-01-00 00:00:00', 'en', 'drama',  '米国ドラマ' ),
    (2, 1, '2020-02-00 00:00:00', 'jp', 'thread', '日本スレッドA' ),
    (3, 2, '2020-03-00 00:00:00', 'jp', 'thread', '日本スレッドB' ),
    (4, 0, '2020-04-00 00:00:00', 'en', 'user',   '米国ユーザー' )
;

# users_table
# 日本ユーザー
CREATE TABLE users_table
    (user_id int, nick_name text)
;  
INSERT INTO users_table
    (user_id, nick_name)
VALUES
    (1, '田中一郎'),
    (2, '田中二郎')
;

###該当のコード
「threadのリスト」を取得するコードです。

下記のコードを実行する（SQL fiddle）

/*
■当SQLの説明
彼（user_id=1）として以下を取得している
➀彼が投稿したthread
➁または彼がfollow|favoriteしたthread
*/

select 
	 c.content_id 
	,c.content_text
	,c.author_id    
    ,case
      when c.author_id=1
      then c.content_date    
     end as his_post_date     
	,max(
		case when a.action_name='follow'
		then a.action_date end 
	) as follow_date
	,max(
		case when a.action_name='favorite'
		then a.action_date end 
	) as favorite_date			
from actions_table a
	
# join
left join contents_table c
on a.target_id=c.content_id
left join users_table u
on a.target_id=u.user_id      
	
# ➀彼が投稿したthread
where c.content_type='thread'
and ( c.author_id=1 )
# ➁または彼がfollow|favoriteしたthread
or (
  a.actor_id=1 and a.target_type='thread'
  and a.action_name in ('favorite', 'follow') 
  and exists(
	select 0 from actions_table
	where target_id=a.target_id
    and ( action_name='follow' or action_name='favorite' )
  ) 
)

group by a.target_id

###取得のロジック
「threadのリスト」を取得するロジックです。

➀彼が投稿したthread

を取得するために、contents_tableを見て、author_id=1かつcontent_type='thread'のものを取得します。

続いて

➁または彼がfollow|favoriteしたthread

を取得するために、actions_tableを見て、actor_id=1かつaction_name='follow'かつtarget_type='thread'のものを取得します。

以上のように「threadのリスト」はたぶん取得できたのではないかと思っています。

###試したこと
本題です。

こうして取得された「threadのリスト」には
・his_post_date（彼が投稿した日）
・follow_date（彼がfollowした日）
・favorite_date（彼がfavoriteした日）
と３つの日付があるのですが、これらをあわせて昇順でソートしたいと思っています。

そこでgreatestを使ってみたのが次のコードです。

先頭のselectの中で3つを併せてsort_dateとし、最後の行でorder by sort_date descを追加したという２点の変更によって実現できる心づもりでおりました。

しかし実現できず、質問させて頂きました。

select 
	 c.content_id 
	,c.content_text
	,c.author_id    
    ,case
      when c.author_id=1
      then c.content_date    
     end as his_post_date     
	,max(
		case when a.action_name='follow'
		then a.action_date end 
	) as follow_date
	,max(
		case when a.action_name='favorite'
		then a.action_date end 
	) as favorite_date		
    ,greatest( his_post_date, follow_date, favorite_date ) as sort_date			
from actions_table a
from actions_table a
	
# join
left join contents_table c
on a.target_id=c.content_id
left join users_table u
on a.target_id=u.user_id      
	
# ➀彼が投稿したthread
where c.content_type='thread'
and ( c.author_id=1 )
# ➁または彼がfollow|favoriteしたthread
or (
  a.actor_id=1 and a.target_type='thread'
  and a.action_name in ('favorite', 'follow') 
  and exists(
	select 0 from actions_table
	where target_id=a.target_id
    and ( action_name='follow' or action_name='favorite' )
  ) 
)

group by a.target_id
order by sort_date desc

###補足情報（FW/ツールのバージョンなど）
phpMyAdmin（4.8.5）で、MySQL（10.0.33-MariaDB）を使っています。

もしかしたらそもそも取得のロジックの考え方が間違っているのかもしれませんが、実現したいことは**『➀➁の条件で「threadのリスト」を取得し、3つの日付でソートする』**ということで変わりありません。

実現のためのお力添え頂けましたら幸甚に存じます。
宜しくお願い致します。

###ソート方法について追記
３つの日付の中で、最大値を基準としたソートを考えています。

例えば現状の「threadのリスト」（該当のコードのSQL fiddleで取得されるもの）では
「日本スレッドA、日本スレッドB」の順で取得されていますが、これを
「日本スレッドB、日本スレッドA」の順にしたいということです。

「日本スレッドA」は最大値が「2020-02-00 00:00:00」であるのに対して、
「日本スレッドB」は最大値が「2020-03-00 00:00:00」だからです。

Answer 1

先ず、MySQLの**greatest()**はその対象にNullが含まれるとNullを返却する仕様です。
ですので先ず、Nullとならないようにします。

それから、同じselectのレベルで他のエイリアス名(follow_date等)では参照できませんのでネストして下さい。
※order by やgroup by はselect項目を参照可能ですからエラーにはなりませんが。

select * 
from (
  select 
         c.content_id 
        ,c.content_text
        ,c.author_id    
        ,case
          when c.author_id=1
          then c.content_date else '1900-01-01 00:00:00'   
         end as his_post_date     
        ,max(
          case when a.action_name='follow'
          then a.action_date else '1900-01-01 00:00:00' end 
         ) as follow_date
        ,max(
          case when a.action_name='favorite'
          then a.action_date else '1900-01-01 00:00:00' end 
         ) as favorite_date     
  from actions_table a
      # join
      left join contents_table c
      on a.target_id=c.content_id
      left join users_table u
      on a.target_id=u.user_id      
        
      # ➀彼が投稿したthread
      where c.content_type='thread'
      and ( c.author_id=1 )
      # ➁または彼がfollow|favoriteしたthread
      or (
        a.actor_id=1 and a.target_type='thread'
        and a.action_name in ('favorite', 'follow') 
        and exists(
        select 0 from actions_table
        where target_id=a.target_id
          and ( action_name='follow' or action_name='favorite' )
        ) 
      )
  group by a.target_id
) step1
order by  greatest(his_post_date, follow_date, favorite_date)

Comments

[ikatako]

こんばんは。今回もどうもありがとうございます。

sazi様の日頃のご指導もあり、なんとか自力でできたコードがこちらです
http://sqlfiddle.com/#!9/0b9b1f/75
（2020/03/02 10:57に少し修正しました）

ご回答のコードはactions_tableを基準としていますよね。それは私の質問に則ってのことかと思いますし、おかげ様で理解も捗りました。

他方で自力でできたコードはcontents_tableを基準としてみました。

この違いですが、使いわけはどのように考えればよろしいのでしょうか。何を根拠にして、基準となるselectを決めればいいのか知りたいです。

ご回答のコードも自力でできたコードも目的の形にはなっていると思うのですが、もしよろしければ後学のために教えて頂けましたら幸いです。

[sazi]

「threadのリスト」で同じことをやってみれば分かります。

[sazi]

それから、日付書式のデータをtextで定義しているのですからNull値の場合に0で置き換えるのはよろしくありません。
日付型に変換するような場合にはエラーになります。
空文字('')にするか、取りうる日付の最小値にする必要があります。
※空文字を日付型に変換するような場合にはエラー考慮が必要ですが。

[ikatako]

>「threadのリスト」で同じことをやってみれば分かります。

すみませんがこちらどういう意味でしょうか？
0で埋めている部分を除けばご回答と同様に「threadのリスト」が取得されていると思うのですが。

せいぜい考え付いたことといえば、レコードが多いものを基準にした方が良いという理由でしょうか？つまりcontents_tableよりもactions_tableの方がレコードが多いので、actions_tableを基準にしたご回答のコードの方が良いということですか？

[ikatako]

>日付書式のデータをtextで定義しているのですからNull値の場合に0で置き換えるのはよろしくありません。

なるほど。ありがとうございます。ざっと調べただけで、ひとまず0を入れておけばいいのかという安直な発想でした。

因みに質問ではTEXTで定義しつつ、実装はDATETIMEとなっておりますが、同じことですよね。（SQLfiddleがDATETIMEだと不具合が多いので質問ではTEXTで定義しました。）

[sazi]

SQL　FILDERに揚げられている内容に、ネストさせずに同じ内容を記述するとエラー（group byがあるから）になります。
それを試してみて下さい。

[ikatako]

select *
from (
/* ココ */
) step1
order by greatest(his_post_date, follow_date, favorite_date)

ご回答頂きましたコードのうち、上記の「ココ」を質問のSQLfiddleに書きました（http://sqlfiddle.com/#!9/0b9b1f/78）が、特にエラーはないようでした。

確認ですが、いまアドバイス頂いているのは22:43の「基準とすべきテーブル
についてactions_tableとcontent_tableとでは何が違い、どう使い分けるべきか」についてですよね？

[ikatako]

いえやはりそれは別の質問とすべきかもしれませんね。質問自体は解決して頂いておりますし、追加でずけずけと大変失礼致しました。

[sazi]

ソートは追加されていないようですが？

[sazi]

実装したいのは、
>こうして取得された「threadのリスト」には
>・his_post_date（彼が投稿した日）
>・follow_date（彼がfollowした日）
>・favorite_date（彼がfavoriteした日）
>と３つの日付があるのですが、これらをあわせて昇順でソートしたいと思っています。
なのではないのですか？

[ikatako]

仰る通りでした。確かに11:15のSQLfiddleの最後の行に「order by greatest(his_post_date, follow_date, favorite_date)」を追加するとエラーとなりました。

とはいえ、このエラーが22:43の「基準とすべきテーブル
についてactions_tableとcontent_tableとでは何が違い、どう使い分けるべきか」という疑問について、どのような意味をもつのかが読み取れないでおります。

[sazi]

エラーの有無は、group byがあるかないかです。（group byがエラーという事では無く、group byではエラーになるSQLになってしまっているから）
group byが必要かどうかは、ikatakoさんの求める仕様次第です。
ネストしておけば、最後にgroup byが無い限りエラーにはなりません。

[ikatako]

やはりgroup byが無いエラーと、先の疑問との関係が読み取れません。ちょっと難しいですね。

[sazi]

> contents_tableよりもactions_tableの方がレコードが多いので、actions_tableを基準にしたご回答のコードの方が良いということですか？

上記が疑問だと思いますが、質問には一意キーなどが含まれておらず、どちらを基準にするのが良いかというのは私には分かりません。
あくまで質問にあるgroup byによるエラーを回避するための回答です。

group byせずに同じ結果が得られるなら、group byしないで済む方が良いとは思いますが。

[ikatako]

なるほど一意キーにもよるのですね。これまでとの兼ね合いもあるので同じ流れでいきたいですし、何にしても頂戴したコードの方が安心ですのでgroup byの方でいきたいと思います。たびたびのご返信をどうもありがとうございました。

Answer 2

「これらをあわせて昇順でソートしたいと思っています。」ですが、どのような基準で解釈するのでしょうか。

• 「ある列が同じなら次な列の比較で判断する」というのであれば、ORDER BY column1, column2のように列挙すれば可能です。
• 「3つの列のうち、最大値を基準にする」ような場合は、ORDER BY MAX(column1, column2, column3)のようにできます。

とりあえず、ソート順を他の人にも伝わるように定義してください。

Comments

[ikatako]

失礼致しました。「最大値を基準にする」でした。質問にその点追記させて頂きました。

[sazi]

order by でMax()は使用できません。