通知 网站从因情语写改为晴雨,这个网站的模板也从calmlog_ex改为 whimurmur

leetcode数据库 系列题 每月交易

358人浏览 / 0人评论 / | 作者:因情语写  | 分类: 数据库  | 标签: 数据库  /  leetcode

作者:因情语写

链接:https://www.qingyu.blue/article/641

声明:请尊重原作者的劳动,如需转载请注明出处


    1193. 每月交易 I

Table: Transactions

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| country       | varchar |
| state         | enum    |
| amount        | int     |
| trans_date    | date    |
+---------------+---------+
id 是这个表的主键。
该表包含有关传入事务的信息。
state 列类型为 “[”批准“,”拒绝“] 之一。

编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。

查询结果格式如下所示:

Transactions table:
+------+---------+----------+--------+------------+
| id   | country | state    | amount | trans_date |
+------+---------+----------+--------+------------+
| 121  | US      | approved | 1000   | 2018-12-18 |
| 122  | US      | declined | 2000   | 2018-12-19 |
| 123  | US      | approved | 2000   | 2019-01-01 |
| 124  | DE      | approved | 2000   | 2019-01-07 |
+------+---------+----------+--------+------------+

Result table:
+----------+---------+-------------+----------------+--------------------+-----------------------+
| month    | country | trans_count | approved_count | trans_total_amount | approved_total_amount |
+----------+---------+-------------+----------------+--------------------+-----------------------+
| 2018-12  | US      | 2           | 1              | 3000               | 1000                  |
| 2019-01  | US      | 1           | 1              | 2000               | 2000                  |
| 2019-01  | DE      | 1           | 1              | 2000               | 2000                  |
+----------+---------+-------------+----------------+--------------------+-----------------------+
select m.month, t.country, count(1) trans_count, sum(case when t.state = 'approved' then 1 else 0 end) approved_count,
    sum(t.amount) trans_total_amount, sum(case when t.state = 'approved' then t.amount else 0 end) approved_total_amount
from Transactions t inner join (
    select distinct date_format(trans_date, '%Y-%m') month
    from Transactions
) m on date_format(t.trans_date, '%Y-%m') = m.month
group by m.month, t.country

    要求每月每个国家/地区的信息,那么先求所有的年月,再按年月,国家/地区分组,利用sum+case, sum求统计信息,学一下多条件group by,date_format,distinct,sum+case

    再看另一种解法

select date_format(trans_date,'%Y-%m') as month,
       country,
       count(state) as trans_count,
       count(case when state='approved' then 1 else null end) as approved_count,
       sum(amount) as trans_total_amount,
       sum(case when state='approved' then amount else 0 end) as approved_total_amount
from Transactions
group by date_format(trans_date,'%Y-%m'),country

    这里没有用连接来获取月份(自己是简单的思路一步步做,要什么就查什么,然后组合起来),直接在group by中获取年月

    1205. 每月交易II

Transactions 记录表

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| id             | int     |
| country        | varchar |
| state          | enum    |
| amount         | int     |
| trans_date     | date    |
+----------------+---------+
id 是这个表的主键。
该表包含有关传入事务的信息。
状态列是类型为 [approved(已批准)、declined(已拒绝)] 的枚举。

Chargebacks 表

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| trans_id       | int     |
| charge_date    | date    |
+----------------+---------+
退单包含有关放置在事务表中的某些事务的传入退单的基本信息。
trans_id 是 transactions 表的 id 列的外键。
每项退单都对应于之前进行的交易,即使未经批准。

编写一个 SQL 查询,以查找每个月和每个国家/地区的已批准交易的数量及其总金额、退单的数量及其总金额。

注意:在您的查询中,给定月份和国家,忽略所有为零的行。

查询结果格式如下所示:

Transactions 表:
+------+---------+----------+--------+------------+
| id   | country | state    | amount | trans_date |
+------+---------+----------+--------+------------+
| 101  | US      | approved | 1000   | 2019-05-18 |
| 102  | US      | declined | 2000   | 2019-05-19 |
| 103  | US      | approved | 3000   | 2019-06-10 |
| 104  | US      | approved | 4000   | 2019-06-13 |
| 105  | US      | approved | 5000   | 2019-06-15 |
+------+---------+----------+--------+------------+

Chargebacks 表:
+------------+------------+
| trans_id   | trans_date |
+------------+------------+
| 102        | 2019-05-29 |
| 101        | 2019-06-30 |
| 105        | 2019-09-18 |
+------------+------------+

Result 表:
+----------+---------+----------------+-----------------+-------------------+--------------------+
| month    | country | approved_count | approved_amount | chargeback_count  | chargeback_amount  |
+----------+---------+----------------+-----------------+-------------------+--------------------+
| 2019-05  | US      | 1              | 1000            | 1                 | 2000               |
| 2019-06  | US      | 3              | 12000           | 1                 | 1000               |
| 2019-09  | US      | 0              | 0               | 1                 | 5000               |
+----------+---------+----------------+-----------------+-------------------+--------------------+
select r.month, r.country, sum(case r.status when 'approved' then r.count else 0 end) approved_count,
    sum(case r.status when 'approved' then r.amount else 0 end) approved_amount,
    sum(case r.status when 'back' then r.count else 0 end) chargeback_count,
    sum(case r.status when 'back' then r.amount else 0 end) chargeback_amount
from(
    select m.month, t.country, count(1) count, sum(t.amount) amount, 'back' status  
    from(
        select distinct date_format(trans_date, '%Y-%m') month
        from Transactions
        union
        select distinct date_format(trans_date, '%Y-%m')
        from Chargebacks
    ) m inner join Chargebacks c on m.month = date_format(c.trans_date, '%Y-%m') 
    inner join Transactions t on t.id = c.trans_id
    group by m.month, t.country
    union
    select m.month, t.country, count(1) count, sum(t.amount) amount, 'approved' 
    from(
        select distinct date_format(trans_date, '%Y-%m') month
        from Transactions
        union
        select distinct date_format(trans_date, '%Y-%m')
        from Chargebacks
    ) m inner join Transactions t on m.month = date_format(t.trans_date, '%Y-%m')  and t.state = 'approved'
    group by m.month, t.country
) r
group by r.month, r.country

    嵌套+连接,分别求出批准和退单信息,union在一起,最后统计一下相关信息,学一下多字段group by,多表连接,union作为from条件,distinct,sum+case求记录数和求数量和

    下面看另一种解法

select date_format(a.trans_date,'%Y-%m') as month,
       a.country as country,
       count(case when a.type='approved' then 1 else null end) as approved_count,
       #sum(case when a.type='approved' then 1 else 0 end) as approved_count,
       sum(case when a.type='approved' then a.amount else 0 end) as approved_amount,
       count(case when a.type='chargeback' then 1 else null end) as chargeback_count,
       #sum(case when a.type='chargeback' then 1 else 0 end) as chargeback_count,
       sum(case when a.type='chargeback' then a.amount else 0 end) as chargeback_amount
from 
(select id,
       country,
       'approved' as type,
       amount,
       trans_date
from Transactions
where state='approved'
union all
select c.trans_id as id,
       t.country as country,
       'chargeback' as type,
       t.amount as amount,
       c.trans_date as trans_date
from Chargebacks c join Transactions t on c.trans_id=t.id
)a
group by date_format(a.trans_date,'%Y-%m'),a.country

    上面的做法太复杂,这种简洁多了,作为中等题,不应该做成上面那么复杂,一定有简洁的解法,思路都是差不多的,分别求批准和退单的情况,连接在一起,最后统计一下相关信息


亲爱的读者:如果你喜欢文章的话,不妨点赞评论支持作者一下吧


点赞(0) 打赏

全部评论

还没有评论!