SQL面试测试

Question

作为面试的一部分，我被问到了以下问题，我很想知道最佳的解决方案。这个问题已经过编辑，因此无法辨认。

问题

表“事务”具有以下结构：

create table transactions (
        sent_from varchar not null,
        receiver varchar not null,
        date date not null,
        usd_value integer not null);

编写一个查询，返回在最多3个事务中接收到至少1024美元的接收者列表。可以有超过3个转帐到该帐户，只要大约3个或更少的交易usd_value总额至少1024美元。表应按名称排序(按升序排列)。

例如，给定数据：

sent_from   | receiver     | date       | usd_value 
------------+--------------+------------+--------
Jonas       | Willhelm     | 2000-01-01 | 200
Jonas       | Timpson      | 2002-09-27 | 1024
Jonas       | Bjorn        | 2001-03-16 | 512
Willhelm    | Bjorn        | 2010-12-17 | 100
Willhelm    | Bjorn        | 2004-03-22 | 10
Brown       | Bjorn        | 2013_03_20 | 500
Bjorn       | Willhelm     | 2007-06-02 | 400
Bjorn       | Willhelm     | 2001-03-16 | 400
Bjorn       | Willhelm     | 2001-03-16 | 200

查询应该返回以下行集：

  account_name
 --------------
  Bjorn
  Taylor

之所以列出Bjorn账户，是因为它在以下三项交易中收到了1112美元512美元+ 100美元+ 500美元= 1112美元。Timpson账户仅在一次转账中就收到了1024美元。Willhelm账户在四笔交易中收到1200美元，但是否没有列出，因为该账户上没有至少1024美元的三笔交易。

我的解决方案：

WITH ordered_transactions AS (
  SELECT
    receiver, usd_value,
    ROW_NUMBER()
    OVER (PARTITION BY
      receiver
      ORDER BY usd_value DESC) AS Row_ID
  FROM public.transactions
)

SELECT receiver FROM
  (SELECT receiver, sum(usd_value) as smount
  FROM ordered_transactions
  WHERE Row_ID < 4
  GROUP BY receiver) AS reduced
WHERE reduced.smount >= 1024
ORDER BY reduced.receiver ASC;

设置数据(postgreSQL)：

--我曾试图在www.sqlfiddle.com - http://sqlfiddle.com/#!15/13fc3/3

create table transactions (
    sent_from    VARCHAR NOT NULL,
    receiver VARCHAR NOT NULL,
    date      DATE    NOT NULL,
    usd_value    INTEGER NOT NULL);


insert into transactions VALUES ('Jonas', 'Willhelm', to_date('2000-01-01', 'YYYY-MM-DD'), 200 );
insert into transactions VALUES ('Jonas', 'Taylor', to_date('2002-09-27', 'YYYY-MM-DD'), 1024 );
insert into transactions VALUES ('Jonas', 'Bjorn', to_date('2001-03-16', 'YYYY-MM-DD'), 512 );
insert into transactions VALUES ('Willhelm', 'Bjorn', to_date('2010-12-17', 'YYYY-MM-DD'), 100 );
insert into transactions VALUES ('Willhelm', 'Bjorn', to_date('2004-03-22', 'YYYY-MM-DD'), 10 );
insert into transactions VALUES ('Brown', 'Bjorn', to_date('2013-03-20', 'YYYY-MM-DD'), 500 );
insert into transactions VALUES ('Bjorn', 'Willhelm', to_date('2007-06-02', 'YYYY-MM-DD'), 400 );
insert into transactions VALUES ('Bjorn', 'Willhelm', to_date('2001-03-16', 'YYYY-MM-DD'), 400 );
insert into transactions VALUES ('Bjorn', 'Willhelm', to_date('2001-03-16', 'YYYY-MM-DD'), 200 );

任何关于我应该如何处理这个问题的暗示都很感激。

Answer 1

您的解决方案似乎不错，但是可以使用having子句简化查询：

SELECT receiver
FROM ordered_transactions
WHERE Row_ID < 4
GROUP BY receiver
HAVING SUM(usd_value) >= 1024
ORDER BY receiver ASC;

Answer 2

我想我会写成：

WITH t AS (
      SELECT t.*,
             ROW_NUMBER() OVER (PARTITION BY receiver ORDER BY usd_value DESC) as seqnum
      FROM public.transactions t
      WHERE usd_value >= 0
     )
SELECT receiver
FROM t
WHERE seqnum <= 3
GROUP BY receiver 
HAVING sum(usd_value) >= 1024
ORDER BY receiver;

关于amount字段中存在负数的问题尚不清楚。如果这是可能的，那么总是取第一个值将是不准确的(一般)。但是，就所提供的数据而言，这是准确的。

我可以看到有人在使用子查询而不是HAVING。但这对性能没有影响(在Postgres中)，而且相当小。

Answer 3

SELECT processed_table.receiver AS account_name, SUM(processed_table.usd_value) AS received_money
FROM

    (
         SELECT temp_table.receiver, temp_table.sent_from, temp_table.usd_value,
         row_number() OVER(PARTITION BY temp_table.receiver ORDER BY 
         temp_table.usd_value 
         DESC) AS row_number 
         FROM transactions AS temp_table
    ) AS processed_table

WHERE processed_table.row_number <=  3
GROUP BY(processed_table.receiver)
HAVING SUM(processed_table.usd_value) >= 1024
ORDER BY processed_table.receiver
;