Процентное изменение зарплаты с помощью BigQuery
Используя bigquery, я должен рассчитать процентное изменение заработной платы с 2020 по 2021 год. У меня есть фиктивные данные о занятости. Я делюсь только несколькими строками для простоты.
Выделенный столбец является обязательным столбцом, и я хочу, чтобы результат был только в этой форме.
Формула = (зарплата_2020 - зарплата_2021) / зарплата_2020 )* 100
My query :
with a_2020(
SELECT * FROM `xx.salary.Sal_2020_2021`
where Year = 2020),
b_2021 as (SELECT * FROM `xx.salary.Sal_2020_2021`
where Year = 2021)
select * , (a_2020.Salary - b_2021 .Salary) / a_2020.Salary )* 100 from b_2021
join a_2020
using (Emp_ID ,Address,Name, State)
Проблема: 1) Я получаю все поля дважды. 2) В итоге получаю только 2021 год. Не 2020
Я пропустил эту часть. Хотя теперь я добавил его.
Вам нужно полное внешнее объединение, если вы хотите включить людей, работающих только один год.
Я бы использовал только Emp_ID в качестве условия соединения. (Иногда люди переезжают на другой адрес.)
Для расчета прибавки возьмите новую зарплату - старую зарплату.
Что делать, если я хочу включить оба года? Любой способ сделать это?
полное внешнее соединение. Или пропустите соединение и вместо этого выполните GROUP BY.
Было бы очень полезно, если бы можно было упомянуть поле для GROUP By .
Ответы 2
Присоединяйтесь к Emp_ID и не включайте повторяющиеся значения для года. Если предположить, что в столбце года указаны только значения 2020 и 2021, этот код может работать.
SELECT
E1.Emp_ID AS Emp_ID,
E1.Name AS Name,
E1.Experience AS Experience,
E1.Year AS Year,
E1.Salary AS Salary,
E1.State AS State,
E1.Address AS Address,
CASE WHEN E1.Year=2021 AND E2.Year=2020 THEN ((E1.Salary-E2.Salary)/E2.Salary)*100 ELSE NULL END AS "Percent Increase in Salary"
FROM `xx.salary.Sal_2020_2021` E1 JOIN `xx.salary.Sal_2020_2021` E2 ON E1.Emp_ID=E2.Emp_ID AND E1.Year <> E2.Year
UNION ALL
SELECT
Emp_ID,
Name,
Experience,
Year,
Salary,
State,
Address,
NULL AS "Percent Increase in Salary"
FROM `xx.salary.Sal_2020_2021`
GROUP BY Emp_ID
HAVING COUNT(*)=1
spb- Этот код очень хорошо. Меня беспокоит только то, что он не показывает строку, если ее значение указано только для одного года. Нравятся записи Ананьи и Армаана, потому что они принадлежат одному году.
mp.kaur - обновлен ответ для обработки записей, принадлежащих одному году.
Вы можете рассмотреть приведенный ниже запрос с использованием оконной функции.
SELECT Emp_ID, Year, Salary,
IF(Year = 2021,
ROUND((Salary / LAG(Salary) OVER w - 1) * 100, 2), NULL
) AS increase
FROM `xx.salary.Sal_2020_2021`
WINDOW w AS (PARTITION BY Emp_ID ORDER BY Year);
+--------+------+--------+----------+
| Emp_ID | Year | Salary | increase |
+--------+------+--------+----------+
| 10003 | 2020 | 15000 | |
| 10003 | 2021 | 16700 | 11.33 |
| 10007 | 2021 | 9000 | |
| 10012 | 2020 | 10000 | |
| 10012 | 2021 | 13500 | 35.0 |
| 10018 | 2021 | 19000 | |
| 10041 | 2021 | 20000 | |
| 10041 | 2021 | 21000 | 5.0 |
+--------+------+--------+----------+
Условие соединения не требуется?