9/20 每日一題(MYSQL 調查收入的高中低位數有多少)

Table: Accounts

+-------------+------+
| Column Name | Type |
+-------------+------+
| account_id  | int  |
| income      | int  |
+-------------+------+
account_id is the primary key (column with unique values) for this table.
Each row contains information about the monthly income for one bank account.

 

Write a solution to calculate the number of bank accounts for each salary category. The salary categories are:

• "Low Salary": All the salaries strictly less than $20000.
• "Average Salary": All the salaries in the inclusive range [$20000, $50000].
• "High Salary": All the salaries strictly greater than $50000.

The result table must contain all three categories. If there are no accounts in a category, return 0.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Accounts table:
+------------+--------+
| account_id | income |
+------------+--------+
| 3          | 108939 |
| 2          | 12747  |
| 8          | 87709  |
| 6          | 91796  |
+------------+--------+
Output: 
+----------------+----------------+
| category       | accounts_count |
+----------------+----------------+
| Low Salary     | 1              |
| Average Salary | 0              |
| High Salary    | 3              |
+----------------+----------------+
Explanation: 
Low Salary: Account 2.
Average Salary: No accounts.
High Salary: Accounts 3, 6, and 8.

 # Write your MySQL query statement below

select 'High Salary' AS category,sum(if((income>50000),1,0)) AS accounts_count

from Accounts

union

select 'Low Salary' AS category,sum(if((income<20000),1,0)) AS accounts_count

from Accounts

union

select 'Average Salary' AS category,sum(if(income between 20000 and 50000,1,0)) AS accounts_count

from Accounts

---------------------------

# Write your MySQL query statement below
select 'Low Salary' as category, count(*) as accounts_count
from Accounts
where income < 20000

UNION 
select 'Average Salary', count(*)
from Accounts 
where income >= 20000 && income <= 50000

UNION
select 'High Salary', count(*)
from Accounts 
where income > 50000

-------------------------pandas

import pandas as pd
def count_salary_categories(accounts: pd.DataFrame) -> pd.DataFrame:
    category_count={'Low Salary':0,'Average Salary':0,'High Salary':0}
    
    for i,row in accounts.iterrows():
        income=row['income']
        if income < 20000:
            category_count['Low Salary'] += 1
        elif income > 50000:
            category_count['High Salary'] += 1
        else:
            category_count['Average Salary'] += 1
    
    #轉乘DataFrame
    res=pd.DataFrame({
        'category':list(category_count),
        'accounts_count':list(category_count.values())
    })
    print(res)
    return res
    --------------------------------參考

import pandas as pd

def count_salary_categories(accounts: pd.DataFrame) -> pd.DataFrame:

    # low salary: less than 20k
    # avg salary: 20k-50k
    # high salary: more than 50k

    low_sal = (accounts["income"] < 20000).sum()
    avg_sal = ((accounts["income"] >= 20000) & (accounts["income"] <= 50000)).sum()
    high_sal = (accounts["income"] > 50000).sum()

    return pd.DataFrame({"category": ["Low Salary", "Average Salary", "High Salary"],"accounts_count": [low_sal, avg_sal, high_sal]})

--------------------參考

import pandas as pd

def count_salary_categories(accounts: pd.DataFrame) -> pd.DataFrame:
    return pd.DataFrame({
        'category': ['Low Salary', 'Average Salary', 'High Salary'],
        'accounts_count': [
            accounts[accounts.income < 20000].shape[0],
            accounts[(accounts.income >= 20000) & (accounts.income <= 50000)].shape[0],
            accounts[accounts.income > 50000].shape[0],
        ],
    })

--------------------------------------------參考

import pandas as pd

def count_salary_categories(accounts: pd.DataFrame) -> pd.DataFrame:
    low_df = accounts[accounts['income'] < 20_000]
    avg_df = accounts[accounts['income'] <= 50_000 ]
    return pd.DataFrame({'category':['Low Salary','Average Salary','High Salary'],
                        'accounts_count':[len(low_df),len(avg_df) - len(low_df),len(accounts) - len(avg_df)]})

----------------------------參考

import pandas as pd

def count_salary_categories(accounts: pd.DataFrame) -> pd.DataFrame:
    low = len(accounts[accounts['income'] < 20000])
    average = len(accounts[(accounts['income'] >= 20000) & (accounts['income'] <= 50000)])
    high = len(accounts[accounts['income'] > 50000])

    data = [['Low Salary', low], ['Average Salary', average], ['High Salary', high]]
    df = pd.DataFrame(data, columns=['category', 'accounts_count'])

    return df