Запрос 2
Найти количество сотрудников, в должности которых фигурирует слово «Manager», найти количество сотрудников, в должности которых фигурирует «Clerk», найти количество сотрудников, в должности которых фигурирует «President», построить гистограмму с 4 столбцами (Manager, Clerk, President, Other), по оси у – максимальная зарплата сотрудников по каждой группе.
Код представлен в листинге 2. Результат представлен на рисунке 3.
Листинг 2 – Вывод максимальной зарплата сотрудников по каждой группе
-
import psycopg2
import pandas as pd
import matplotlib.pyplot as plt
# Connect to the database
conn = psycopg2.connect(database="students", user="postgres", password="123", host="127.0.0.1", port="5432")
cursor = conn.cursor()
# Run the SQL query
query = "SELECT e.department_id, MIN(e.salary) as min_salary, m.last_name as manager_last_name " \
"FROM employees e " \
"JOIN employees m ON e.manager_id = m.employee_id " \
"GROUP BY e.department_id, m.last_name "
cursor.execute(query)
# Fetch the result and store it in a Pandas dataframe
result = cursor.fetchall()
df = pd.DataFrame(result, columns=["department_id", "min_salary", "manager_last_name"])
# Plot the data
plt.bar(df["manager_last_name"], df["min_salary"])
plt.xlabel("Фамилия менеджера")
plt.ylabel("Минимальная заработная плата")
plt.title("Минимальная зарплата по менеджерам")
plt.show()
# Close the cursor and connection
cursor.close()
conn.close()
Этот запрос сначала создает общее табличное выражение (CTE - Common Table Expression), которое объединяет таблицы "employees" и "jobs" по столбцу "job_id" и выбирает столбцы "job_id", "job_title" и "salary". Затем основной запрос выбирает новый столбец "job_group" на основе того, содержит ли столбец "job_title" слова "Manager", "Clerk", "President" или ни одного из них, используя оператор CASE. Затем запрос группирует записи по столбцу "job_group" и выбирает максимальную зарплату в каждой группе. Наконец, код использует matplotlib для создания гистограммы с 4 столбцами (по одному на каждую группу должностей), где ось x представляет группу должностей, а ось y - максимальную зарплату в этой группе.
Рисунок
3 – Вывод максимальной зарплата
сотрудников по каждой группе
Сделать гистограмму горизонтальной и изменить цвета для предыдущих графиков
Изменённый код для 1 запроса представлен в листинге 3. Результат выполнения запроса представлен на рисунке 4.
Листинг 3 – Изменённый вывод минимальной зарплаты по менеджерам
-
import psycopg2
import pandas as pd
import matplotlib.pyplot as plt
# Connect to the database
conn = psycopg2.connect(database="students", user="postgres", password="123", host="127.0.0.1", port="5432")
cursor = conn.cursor()
# Run the SQL query
query = "SELECT e.department_id, MIN(e.salary) as min_salary, m.last_name as manager_last_name " \
"FROM employees e " \
"JOIN employees m ON e.manager_id = m.employee_id " \
"GROUP BY e.department_id, m.last_name "
cursor.execute(query)
# Fetch the result and store it in a Pandas dataframe
result = cursor.fetchall()
df = pd.DataFrame(result, columns=["department_id", "min_salary", "manager_last_name"])
# Plot the data
plt.barh(df["manager_last_name"], df["min_salary"], color='red')
plt.xlabel("Фамилия менеджера")
plt.ylabel("Минимальная заработная плата")
plt.title("Минимальная зарплата по менеджерам")
plt.show()
# Close the cursor and connection
cursor.close()
conn.close()
Рисунок
4 – Изменённый
Изменённый код для 2 запроса представлен в листинге 4. Результат выполнения запроса представлен на рисунке 5.
Листинг 4 – Изменённый вывод максимальной зарплаты сотрудников по каждой группе
-
import matplotlib.pyplot as plt
import numpy as np
import psycopg2
conn = psycopg2.connect(
host="127.0.0.1",
database="students",
user="postgres",
password="123",
port="5432"
)
cur = conn.cursor()
cur.execute("""
WITH cte AS (
SELECT employees.job_id, job_title, salary
FROM employees
JOIN jobs ON employees.job_id = jobs.job_id
)
SELECT
CASE
WHEN job_title LIKE '%Manager%' THEN 'Manager'
WHEN job_title LIKE '%Clerk%' THEN 'Clerk'
WHEN job_title LIKE '%President%' THEN 'President'
ELSE 'Other'
END AS job_group,
MAX(salary) AS max_salary
FROM cte
GROUP BY job_group
ORDER BY max_salary DESC
""")
results = cur.fetchall()
x = [result[0] for result in results]
y = [result[1] for result in results]
plt.barh(x, y, color=['red', 'green', 'blue', 'purple'])
plt.xlabel("Максимальная зарплата")
plt.ylabel("Группа по должностям")
plt.title("Максимальная зарплата сотрудников по каждой группе")
plt.show()
cur.close()
conn.close()
Рисунок
5 – Изменённый вывод максимальной
зарплаты сотрудников по каждой группе