distinct和distinct-on的使用

去重；关键字 distinct 去重功能 在其他数据库（oracle，mysql）是存在；当然 postgresql 也有这个功能

[postgres@sdserver40_210 ~]$ psql mydb lottu
psql (9.5.0)
Type "help" for help.

mydb=> select * from trade;
 tradeno | accountid  | fee | game_id
---------+------------+-----+---------
 1000006 | yyb_100001 |  10 |    2555
 1000011 | yyb_100002 | 100 |    2555
 1001859 | yyb_100001 |  10 |    2555
 1001861 | yyb_100003 |  20 |    2555
 1001854 | yyb_100004 |   6 |    2555
 1001881 | yyb_100002 | 328 |    2555
(6 rows)

mydb=> select distinct accountid from trade;
 accountid
------------
 yyb_100001
 yyb_100004
 yyb_100002
 yyb_100003
(4 rows)

mydb=> select distinct accountid,game_id from trade;
 accountid  | game_id
------------+---------
 yyb_100001 |    2555
 yyb_100003 |    2555
 yyb_100004 |    2555
 yyb_100002 |    2555
(4 rows)

跟 on 一起用； 使用 DISTINCT ON 实现用窗口函数实现的取第一名的功能

这个功能 oracle，mysql 是没有的；当然它们有其他的分析函数可以替换；顶替；例如 row_number, fisrt_value 等等

mydb=> select distinct on (accountid) accountid,fee from trade;
 accountid  | fee
------------+-----
 yyb_100001 |  10
 yyb_100002 | 100
 yyb_100003 |  20
 yyb_100004 |   6
(4 rows)

mydb=> select distinct on (game_id) accountid,fee from trade;
 accountid  | fee
------------+-----
 yyb_100001 |  10
(1 row)

mydb=> select distinct on (game_id) accountid,fee from trade order by game_id, fee desc;
 accountid  | fee
------------+-----
 yyb_100002 | 328
(1 row)

--例如取每个帐号充值最大的一笔
mydb=> select distinct on (accountid) accountid,fee from trade order by accountid, fee desc;
 accountid  | fee
------------+-----
 yyb_100001 |  10
 yyb_100002 | 328
 yyb_100003 |  20
 yyb_100004 |   6
(4 rows)

在 typeorm 中实现

import {
    Repository, Between, MoreThanOrEqual, In, LessThan, IsNull, LessThanOrEqual
    } from 'typeorm';


this.transactionRepository
        .createQueryBuilder()
        .select(`DISTINCT ON ("accountId") "accountId"`)
        .addSelect(['"transactionTime"', '"recipientCost"'])
        .where({
          status: TransactionStatusEnum.Closed,
          type: TransactionTypeEnum.Deposit,
          accountId: In(_accountIds),
          senderType: IsNull(),
          transactionTime: Between(time[0], time[1]),
        })
        .orderBy(`"accountId", "transactionTime"`, 'ASC')
        .getRawMany(),