列表

详情

1907. 按分类统计薪水

表: Accounts

+-------------+------+
| 列名        | 类型  |
+-------------+------+
| account_id  | int  |
| income      | int  |
+-------------+------+
account_id 是这个表的主键。
每一行都包含一个银行帐户的月收入的信息。

 

写出一个 SQL 查询，来报告每个工资类别的银行账户数量。 工资类别如下：

结果表 必须 包含所有三个类别。 如果某个类别中没有帐户，则报告 0 。

按 任意顺序 返回结果表。

查询结果格式如下示例。

 

示例 1：

输入：
Accounts 表:
+------------+--------+
| account_id | income |
+------------+--------+
| 3          | 108939 |
| 2          | 12747  |
| 8          | 87709  |
| 6          | 91796  |
+------------+--------+
输出：
+----------------+----------------+
| category       | accounts_count |
+----------------+----------------+
| Low Salary     | 1              |
| Average Salary | 0              |
| High Salary    | 3              |
+----------------+----------------+
解释：
低薪: 数量为 2.
中等薪水: 没有.
高薪: 有三个账户，他们是 3, 6和 8.

原站题解

去查看

上次编辑到这里，代码来自缓存 点击恢复默认模板
# Write your MySQL query statement below

pythondata 解法, 执行用时: 384 ms, 内存消耗: 82 MB, 提交时间: 2023-08-09 15:29:03 

import pandas as pd

def count_salary_categories(accounts: pd.DataFrame) -> pd.DataFrame:
    low_count = (accounts['income'] < 20000).sum()
    average_count = ((accounts['income'] >= 20000) & (accounts['income'] <= 50000)).sum()
    high_count = (accounts['income'] > 50000).sum()

    ans = pd.DataFrame({
        'category': ['Low Salary', 'Average Salary', 'High Salary'],
        'accounts_count': [low_count, average_count, high_count]
    })

    return ans

pythondata 解法, 执行用时: 4092 ms, 内存消耗: 87.5 MB, 提交时间: 2023-08-09 15:28:29 

import pandas as pd

def count_salary_categories(accounts: pd.DataFrame) -> pd.DataFrame:
    # 根据工资列创建三个工资类别
    salary_categories = ['Low Salary', 'Average Salary', 'High Salary']
    
    # 创建一个字典，用于存储每个工资类别的账户数量
    counts = {category: 0 for category in salary_categories}
    
    # 遍历每一行数据，根据收入确定工资类别并进行计数
    for index, row in accounts.iterrows():
        income = row['income']
        if income < 20000:
            counts['Low Salary'] += 1
        elif 20000 <= income <= 50000:
            counts['Average Salary'] += 1
        else:
            counts['High Salary'] += 1
    
    # 创建结果DataFrame
    result = pd.DataFrame(counts.items(), columns=['category', 'accounts_count'])
    
    return result

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

# Write your MySQL query statement below
WITH b AS (
    SELECT 'Low Salary' AS category
    UNION ALL 
    SELECT 'Average Salary' AS category
    UNION ALL
    SELECT 'High Salary' AS category
)

SELECT b.category, IFNULL(COUNT(DISTINCT account_id), 0) AS accounts_count
FROM
(SELECT account_id, 
CASE    
    WHEN income < 20000 THEN 'Low Salary'
    WHEN income >= 20000 and income <= 50000 THEN 'Average Salary'
    ELSE 'High Salary'
END AS category
FROM Accounts) a
RIGHT JOIN b
ON a.category = b.category
GROUP BY b.category

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

# Write your MySQL query statement below
select 'Low Salary' as category ,sum(if (income <20000,1,0)) as accounts_count from accounts
union all
select 'Average Salary' as category ,sum(if (income>=20000 and income <=50000,1,0)) as accounts_count from accounts
union all
select 'High Salary' as category ,sum(if (income >50000,1,0)) as accounts_count from accounts;

mysql 解法, 执行用时: 1233 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 11:59:41 

# Write your MySQL query statement below
with t as(
    select 'Low Salary' category 
    union 
    select 'Average Salary' 
    union 
    select 'High Salary'
)
select
    t.category, ifnull(a.cnt,0) accounts_count
from
    t
left join
    (
        select 
            case when income  < 20000 then 'Low Salary'
                 when income  > 50000 then 'High Salary'
                 else 'Average Salary' end as category,
            count(1) as cnt
        from
            Accounts 
        group by 1
    ) a
on
    t.category = a.category;

上一题

© 2024 leetcode.cn