#mysql #sum
#mysql #сумма
Вопрос:
У меня есть таблица db «записи» с журналами учета рабочего времени сотрудников.
employee_id | hours | task_id | logged_at
---------------------------------------------------------------------
1 | .5 | 1 | 1546342200 (jan 19)
2 | .8 | 2 | 1547548200 (jan 19)
1 | .2 | 3 | 1549022400 (feb 19)
1 | .5 | 3 | 1549022400 (feb 19)
2 | .9 | 1 | 1551625200 (mar 19)
Моя цель — получить отчет, в котором будут указаны оплачиваемые и неоплачиваемые часы за каждый месяц года. Оплачиваемый или неоплачиваемый определяется идентификатором задачи. У меня есть список идентификаторов задач, которые оплачиваются, и список, которые не оплачиваются.
Цель для конечного результата
month | year | total | billable | nonbillable
-------------------------------------------------------------------
1 | 2019 | 1.3 | .8 | .5
2 | 2019 | .7 | .7 | 0
3 | 2019 | .9 | 0 | .9
Это основано на том, что задачи 2 и 3 оплачиваются, а задача 1 не оплачивается.
Пожалуйста, обратите внимание, что это упрощение реальной базы данных, которая имеет около 25 различных идентификаторов задач, которые иногда меняются.
Я смог реализовать общую идею, но застрял на разделении оплачиваемых часов и часов, не подлежащих оплате, на основе task_id.
Это все, что я получил:
SELECT
MONTH(FROM_UNIXTIME(logged_at)),
YEAR(FROM_UNIXTIME(logged_at)),
SUM(hours)
FROM dpd_harvest_entries
GROUP BY
MONTH(FROM_UNIXTIME(logged_at)),
YEAR(FROM_UNIXTIME(logged_at))
Ответ №1:
Я предполагаю, что у вас есть tasks
таблица со столбцом billable
, который содержит 1 для оплачиваемых задач и 0 для не оплачиваемых задач. тогда ваш запрос мог бы выглядеть как:
SELECT
MONTH(FROM_UNIXTIME(logged_at)),
YEAR(FROM_UNIXTIME(logged_at)),
SUM(hours) total,
SUM(IF(t.billable,hours,0)) billable,
SUM(IF(t.billable,0,hours)) nonbillable
FROM dpd_harvest_entries e
INNER JOIN tasks t
ON e.task_id = t.task_id
GROUP BY
MONTH(FROM_UNIXTIME(logged_at)),
YEAR(FROM_UNIXTIME(logged_at))
Если вы просто разделили их запятой ids
, вы можете преобразовать этот запрос в:
SELECT
MONTH(FROM_UNIXTIME(logged_at)),
YEAR(FROM_UNIXTIME(logged_at)),
SUM(hours) total,
SUM(IF(task_id IN ({your id list}),hours,0)) billable,
SUM(IF(task_id IN ({your id list}),0,hours)) nonbillable
FROM dpd_harvest_entries e
GROUP BY
MONTH(FROM_UNIXTIME(logged_at)),
YEAR(FROM_UNIXTIME(logged_at))
Где {your id list}
следует заменить на ex: IN (1, 4, 55, 47)
И для будущего использования я бы настоятельно рекомендовал, ALTER TABLE dpd_harvest_entries MODIFY logged_at DATETIME
что упростит многие запросы и позволит избавиться от FROM_UNIXTIME
вызовов повсюду.
Ответ №2:
Это должно сработать..
select a.month, a.year, (billable non_billable) total, billable, non_billable
from (select month, year, sum(hours) billable
from dpd_harvest_entries
where task_id in (2, 3)
group by month, year) a,
(select month, year, sum(hours) non_billable
from dpd_harvest_entries
where task_id in (1))
group by month, year) b
where a.month = b.month
and a.year = b.year
Я предполагаю, что вы можете преобразовать временные метки в месяц и год
Ответ №3:
Вам понадобятся 2 подзапроса (оплачиваемый и не оплачиваемый), в обоих из них есть столбец «год-месяц», а затем объедините их в своем столбце «год-месяц», чтобы объединить их, что-то вроде этого:
SELECT
billable.year_month as year_month,
billable.sum_hours as billable_hours,
non_billable.sum_hours as non_billable_hours,
(billable.sum_hours non_billable.sum_hours) as total_hours
FROM (SELECT
DATE_FORMAT(FROM_UNIXTIME(logged_at), '%Y-%m') as year_month,
SUM(hours) as sum_hours
FROM dpd_harvest_entries
WHERE task_id IN (1,2,3)
GROUP BY
1) as billable,
(SELECT
DATE_FORMAT(FROM_UNIXTIME(logged_at), '%Y-%m') as year_month,
SUM(hours) as sum_hours
FROM dpd_harvest_entries
WHERE task_id IN (4,5,6)
GROUP BY
1) as non_billable
LEFT JOIN billable on billable.year_month = non_billable.year_month
Будьте осторожны с типом соединения здесь; это приведет к пропуску месяцев, если у вас нет оплачиваемых или неоплачиваемых часов в месяц. Не уверен, какой тип соединения вам понадобится в вашем случае.