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 |
±---------±--------±---------------±----------------±------------------±-------------------+
来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/monthly-transactions-ii
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
思路
- 1.这道题需要用到union all
- UNION 对两个结果集进行并集操作,不包括重复行去重,同时进行默认规则的排序
- UNION ALL 对两个结果集进行并集操作,包括重复行,不进行排序
- 由于Union需要对查询结果集进行排序操作,当数据量较大时,若非特殊需要,尽量不要使用Union操作,而改用Union All操作,
- 然后对Union All出来的结果执行去重操作即可,这样会使得查询的效率大大的增强
- 2.先找出退款的订单,使用inner join,代码如下
SELECT
DATE_FORMAT( c.trans_date, '%Y-%m' ) AS MONTH,
t.country,
amount,
'chargeback' AS type
FROM
transactions AS t
INNER JOIN chargebacks AS c ON t.id = c.trans_id
- 3.然后找出已批准交易订单
SELECT DATE_FORMAT(t.trans_date,'%Y-%m') as MONTH,
t.country,amount,
'approved' as type
FROM transactions as t
WHERE state='approved'
4.合并两个 select 语句结果集
SELECT MONTH AS MONTH
,
country AS COUNTRY,
SUM( IF ( type = 'approved', 1, 0 ) ) AS APPROVED_COUNT,
SUM( IF ( type = 'approved', amount, 0 ) ) AS APPROVED_AMOUNT,
SUM( IF ( type = 'chargeback', 1, 0 ) ) AS CHARGEBACK_COUNT,
SUM( IF ( type = 'chargeback', amount, 0 ) ) AS CHARGEBACK_AMOUNT
FROM
(
(
SELECT
DATE_FORMAT( t.trans_date, '%Y-%m' ) AS MONTH,
t.country,
amount,
'approved' AS type
FROM
transactions AS t
WHERE
state = 'approved'
) UNION ALL
(
SELECT
DATE_FORMAT( c.trans_date, '%Y-%m' ) AS MONTH,
t.country,
amount,
'chargeback' AS type
FROM
transactions AS t
INNER JOIN chargebacks AS c ON t.id = c.trans_id
)
) AS tt
GROUP BY
tt.`month`,
tt.country;
569.员工薪水中位数
Employee 表包含所有员工。Employee 表有三列:员工Id,公司名和薪水。
±----±-----------±-------+
|Id | Company | Salary |
±----±-----------±-------+
|1 | A | 2341 |
|2 | A | 341 |
|3 | A | 15 |
|4 | A | 15314 |
|5 | A | 451 |
|6 | A | 513 |
|7 | B | 15 |
|8 | B | 13 |
|9 | B | 1154 |
|10 | B | 1345 |
|11 | B | 1221 |
|12 | B | 234 |
|13 | C | 2345 |
|14 | C | 2645 |
|15 | C | 2645 |
|16 | C | 2652 |
|17 | C | 65 |
±----±-----------±-------+
请编写SQL查询来查找每个公司的薪水中位数。挑战点:你是否可以在不使用任何内置的SQL函数的情况下解决此问题。
±----±-----------±-------+
|Id | Company | Salary |
±----±-----------±-------+
|5 | A | 451 |
|6 | A | 513 |
|12 | B | 234 |
|9 | B | 1154 |
|14 | C | 2645 |
±----±-----------±-------+
来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/median-employee-salary
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
方法一思路
1.对每个公司 group by 分组,确定中位数区间,统计每组的个数为N,当N为偶数时中位数为下标floor((N-1)/2)和N/2位置处的平均是,下标从 0 开始
SELECT
e.Company,
FLOOR( ( count( * ) - 1 ) / 2 ) AS beg,
IF
( count( * ) % 2 = 1, 0, 1 ) AS cnt
FROM
employee AS e
GROUP BY
e.Company
- 2.计算排名
- 薪水从小的到大排序
- e2.salary 的数量就是e1的排名
SELECT E1.Id,E1.Company,E1.Salary, COUNT(E2.Salary) AS `trank`
FROM employee AS E1
LEFT JOIN employee AS E2
ON(E1.company = E2.company AND (E1.salary = E2.salary AND e1.Id>e2.Id or e1.Salary > e2.Salary))
GROUP BY E1.Id,E1.Company,E1.Salary
ORDER BY E1.Company,E1.Salary
- 3.连接两表,排名在[beg, beg+1]之间的行为中位数
SELECT
b.id,
b.company,
b.Salary
FROM
(
SELECT
e.Company,
FLOOR( ( count( * ) - 1 ) / 2 ) AS beg,
IF
( count( * ) % 2 = 1, 0, 1 ) AS cnt
FROM
employee AS e
GROUP BY
e.Company
) AS a
JOIN (
SELECT
e1.id,
e1.Company,
e1.Salary,
count( e2.Salary ) AS trank
FROM
employee AS e1
LEFT JOIN employee AS e2 ON (
e1.Company = e2.Company
AND ( e1.Salary = e2.Salary AND e1.id > e2.id OR e1.Salary > e2.Salary ) )
GROUP BY
e1.id,
e1.Company,
e1.Salary
ORDER BY
e1.Company,
e1.Salary
) AS b ON ( a.Company = b.Company AND b.trank BETWEEN a.beg AND ( a.beg + a.cnt ) );