SQLにおける3つの重複排除手法の概要

-- list all unique values of task_id (de-duplicated records)
-- select distinct task_id
-- from Task;

-- total number of tasks
select count(distinct task_id) task_num
from Task;

-- list all unique values of task_id (de-duplicated records, null is also a value)
-- select task_id
-- from Task
-- group by task_id;

-- total number of tasks
select count(task_id) task_num
from (select task_id
      from Task
      group by task_id) tmp;

-- use in sql with window function support
select count(case when rn=1 then task_id else null end) task_num
from (select task_id
       , row_number() over (partition by task_id order by start_time) rn
   from Task) tmp;

-- semicolon below; to separate rows
select distinct user_id
from Test; -- returns 1; 2

select distinct user_id, user_type
from Test; -- returns 1, 1; 1, 2; 2, 1

select user_id
from Test
group by user_id; -- return 1; 2

select user_id, user_type
from Test
group by user_id, user_type; -- returns 1, 1; 1, 2; 2, 1

select user_id, user_type
from Test
group by user_id;    
-- Hive, Oracle, etc. will report an error, mysql can write it like this.
-- Returns 1, 1 or 1, 2 ; 2, 1 (two rows in total). Only the fields after group by will be de-duplicated, that is, the number of records returned at the end is equal to the number of records in the previous sql, i.e. 2
-- fields that are not placed after group by but are placed in select, only one row will be returned (it seems to be the first one, so there is no pattern)