每月交易 I

1193. 每月交易 I

Table: Transactions

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

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

以 任意顺序 返回结果表。

查询结果格式如下所示。

示例 1:

输入：
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 |
+------+---------+----------+--------+------------+
输出：
+----------+---------+-------------+----------------+--------------------+-----------------------+
| 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                  |
+----------+---------+-------------+----------------+--------------------+-----------------------+

原站题解

去查看

上次编辑到这里，代码来自缓存点击恢复默认模板

# Write your MySQL query statement below

pythondata 解法, 执行用时: 446 ms, 内存消耗: 67.2 MB, 提交时间: 2024-05-27 12:55:30

import pandas as pd

def monthly_transactions(transactions: pd.DataFrame) -> pd.DataFrame:
    transactions['month'] = transactions['trans_date'].dt.strftime("%Y-%m")
    transactions['state'] = transactions['state'].apply(lambda x: 1 if x=='approved' else 0)
    transactions['approved_amount']=transactions['state']*transactions['amount']
    transactions_agg = transactions.groupby(['month','country'],dropna=False).agg(trans_count=('id','size'),approved_count=('state','sum'),trans_total_amount=('amount','sum'),approved_total_amount=('approved_amount','sum')).reset_index()
    return transactions_agg

mysql 解法, 执行用时: 372 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 12:09:17

# Write your MySQL query statement below
select substr(trans_date,1,7) as month,
country,
count(country) as trans_count,
count(case when state = 'approved' then state 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 substr(trans_date,1,7),country;

mysql 解法, 执行用时: 405 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 12:08:14

# Write your MySQL query statement below
SELECT DATE_FORMAT(trans_date, '%Y-%m') AS month,
    country,
    COUNT(*) AS trans_count,
    COUNT(IF(state = 'approved', 1, NULL)) AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount
FROM Transactions
GROUP BY month, country;

上一题

下一题

详情