Studio ODIN

PostgreSQL �� DISTINCT ON

PostgreSQL �ɂ� �ʏ�� DISTINCT �ł͂Ȃ� DISTINCT ON �Ƃ������̂�����܂��B

���̂悤�ȃe�[�u��������Ƃ��܂��B

db1=# SELECT * FROM distinct_test;

 field1 | field2 | field3
--------+--------+--------
 1      |      4 |      1
 1      |      4 |      2
 2      |      2 |      1
 2      |      2 |      2
 3      |     -1 |      1
 3      |     -1 |      2
(6 rows)

�܂��͒ʏ�� DISTINCT �ł��B

db1=# SELECT DISTINCT field1, field2
      FROM distinct_test;

 field1 | field2
--------+--------
 1      |      4
 2      |      2
 3      |     -1
(3 rows)

���̏ꍇ�A��Ɏw�肵�� field1, field2 �̏��ԂŃ\�[�g����܂��B

��̎w��̏��Ԃ�ς��܂��B

db1=# SELECT DISTINCT field2, field1
      FROM distinct_test;

 field2 | field1
--------+--------
     -1 | 3
      2 | 2
      4 | 1
(3 rows)

���x�� field2, field1 �Ń\�[�g����܂��B
����͂܂��A�����������̂ł��B

DISTINCT ON ���g���Ă݂܂��B
DISTINCT ON (field1, field2) �̃J�b�R�̒��� ��̎w�� "field1, field2, field3" �� �Ⴄ���Ƃɒ��ӂ��Ă��������B

db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3
      FROM distinct_test;

 field1 | field2 | field3
--------+--------+--------
 1      |      4 |      1
 2      |      2 |      1
 3      |     -1 |      1
(3 rows)

�s���� DISTINCT �� field1, field2 ���w�肵���Ƃ��Ɠ����ł��B

�‚܂� DISTINCT ON ���g�p����� �J�b�R�̒��Ŏw�肵����� �܂Ƃ߂��A�J�b�R�̒��Ŏw�肵�Ă��Ȃ���i���̗Ⴞ�� field3 �j�� �ŏ��Ɍ��‚��������̂��o�͂���܂��B

�u DISTINCT �ł܂Ƃ߂������ǁA���̗���~�����I�v�Ƃ����Ƃ��ɕ֗��ł��ˁB �����A���̂܂܂��� DISTINCT ON �Ɏw�肵�Ă��Ȃ���� �ǂ̒l���o��̂��ۏႳ��Ȃ����߁A�\�[�g�������Ă��K�v������܂��B

ORDER BY �ɂ��\�[�g�͎��̂悤�ɂȂ�܂��B

db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3
       FROM distinct_test
       ORDER BY field1;

 field1 | field2 | field3
--------+--------+--------
 1      |      4 |      1
 2      |      2 |      1
 3      |     -1 |      1
(3 rows)

db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3
      FROM distinct_test
      ORDER BY field2;

ERROR: SELECT DISTINCT ON expressions must match initial
ORDER BY expressions

db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3
      FROM distinct_test
      ORDER BY field3;

ERROR: SELECT DISTINCT ON expressions must match initial
ORDER BY expressions

DISTINCT ON �̐擪�Ŏw�肳��Ă��� field1 �ȊO�� ��ł� �G���[�ɂȂ��Ă��܂��܂��B

ORDER BY �Ń\�[�g����ɂ́A DISTINCT ON �Ŏw�肵����i�w�菇�j�̌�ɁADISTINCT ON �ȊO�̗�������K�v������܂��B

db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3
       FROM distinct_test
       ORDER BY field1, field2, field3 DESC;

 field1 | field2 | field3
--------+--------+--------
 1      |      4 |      2
 2      |      2 |      2
 3      |     -1 |      2
(3 rows)

�������邱�Ƃɂ���� GROUP BY �� MAX() ���g�����̂� �����l���擾�����Ƃ��ł��܂����B