Возвращаемые данные за каждый день недели (7 записей для каждой строки)- MySQL

Question

Запрос, который у меня есть на данный момент, дает мне данные за выбранную дату. Я хотел бы иметь процент за каждый день недели от выбранной даты в течение следующих 7 дней:

select distinct(shift_report.advisor), team, shift_report.date as week_commencing, SUM(shift_report.time) as total_time, round(SUM(shift_report.time)/450 * 100,2) as percentage 
from shift_report
where `date` >=20160223
AND `date`<=20160301
AND `team`=4
GROUP BY shift_report.advisor ORDER BY percentage DESC;

Я бы хотел увидеть:

Советник | Команда | неделя начинается | total_time | % день 1 | % день 2 | % день 3 и так далее в течение 7 дней

Answer 1

Удалось заставить это работать, используя следующее:

SELECT advisor, 
MIN(shift_report.date) AS week_commencing, 
ROUND(100 * SUM(IF(shift_report.date = %%sdate%%, shift_report.time, 0)) / 450,2) AS Day1, 
ROUND(100 * SUM(IF(shift_report.date = %%sdate%% + INTERVAL 1 DAY, shift_report.time, 0)) / 450,2) AS Day2, 
ROUND(100 * SUM(IF(shift_report.date = %%sdate%% + INTERVAL 2 DAY, shift_report.time, 0)) / 450,2) AS Day3, 
ROUND(100 * SUM(IF(shift_report.date = %%sdate%% + INTERVAL 3 DAY, shift_report.time, 0)) / 450,2) AS Day4, 
ROUND(100 * SUM(IF(shift_report.date = %%sdate%% + INTERVAL 4 DAY, shift_report.time, 0)) / 450,2) AS Day5, 
ROUND(100 * SUM(IF(shift_report.date = %%sdate%% + INTERVAL 5 DAY, shift_report.time, 0)) / 450,2) AS Day6, 
ROUND(100 * SUM(IF(shift_report.date = %%sdate%% + INTERVAL 6 DAY, shift_report.time, 0)) / 450,2) AS Day7 
FROM shift_report WHERE date >=%%sdate%% 
%%team%% 
GROUP BY shift_report.advisor;

ПРИМЕЧАНИЕ. %%sdate%% и %%team%% заменяются данными, переданными со страницы php.

Answer 2

Вы можете использовать оператор условия в агрегатной функции SUM, поэтому рассчитывайте для каждого дня:-

SELECT shift_report.advisor, 
    shift_report.team, 
    MIN(shift_report.`date`) AS week_commencing, 
    SUM(shift_report.time) as total_time, 
    ROUND(SUM(shift_report.time)/450 * 100,2) as percentage, 
    ROUND(100 * SUM(IF(shift_report.`date` = 20160223, shift_report.time, 0)) / SUM(shift_report.time), 2) AS Day1,
    ROUND(100 * SUM(IF(shift_report.`date` = 20160224, shift_report.time, 0)) / SUM(shift_report.time), 2) AS Day2,
    ROUND(100 * SUM(IF(shift_report.`date` = 20160225, shift_report.time, 0)) / SUM(shift_report.time), 2) AS Day3,
    ROUND(100 * SUM(IF(shift_report.`date` = 20160226, shift_report.time, 0)) / SUM(shift_report.time), 2) AS Day4,
    ROUND(100 * SUM(IF(shift_report.`date` = 20160227, shift_report.time, 0)) / SUM(shift_report.time), 2) AS Day5,
    ROUND(100 * SUM(IF(shift_report.`date` = 20160228, shift_report.time, 0)) / SUM(shift_report.time), 2) AS Day6,
    ROUND(100 * SUM(IF(shift_report.`date` = 20160229, shift_report.time, 0)) / SUM(shift_report.time), 2) AS Day7,
    ROUND(100 * SUM(IF(shift_report.`date` = 20160301, shift_report.time, 0)) / SUM(shift_report.time), 2) AS Day8
FROM shift_report
WHERE `date` >=20160223
AND `date`<=20160301
AND `team`=4
GROUP BY shift_report.advisor, 
        shift_report.team
ORDER BY percentage DESC;

РЕДАКТИРОВАТЬ

Если вы просто хотите вставить 1 дату, вы можете сделать это следующим образом:-

SELECT shift_report.advisor, 
    shift_report.team, 
    MIN(shift_report.`date`) AS week_commencing, 
    SUM(shift_report.time) as total_time, 
    ROUND(SUM(shift_report.time)/450 * 100,2) as percentage, 
    ROUND(100 * SUM(IF(shift_report.`date` = start_date, shift_report.time, 0)) / SUM(shift_report.time), 2) AS Day1,
    ROUND(100 * SUM(IF(shift_report.`date` = plus_1_date, shift_report.time, 0)) / SUM(shift_report.time), 2) AS Day2,
    ROUND(100 * SUM(IF(shift_report.`date` = plus_2_date, shift_report.time, 0)) / SUM(shift_report.time), 2) AS Day3,
    ROUND(100 * SUM(IF(shift_report.`date` = plus_3_date, shift_report.time, 0)) / SUM(shift_report.time), 2) AS Day4,
    ROUND(100 * SUM(IF(shift_report.`date` = plus_4_date, shift_report.time, 0)) / SUM(shift_report.time), 2) AS Day5,
    ROUND(100 * SUM(IF(shift_report.`date` = plus_5_date, shift_report.time, 0)) / SUM(shift_report.time), 2) AS Day6,
    ROUND(100 * SUM(IF(shift_report.`date` = plus_6_date, shift_report.time, 0)) / SUM(shift_report.time), 2) AS Day7,
    ROUND(100 * SUM(IF(shift_report.`date` = end_date, shift_report.time, 0)) / SUM(shift_report.time), 2) AS Day8
FROM shift_report
INNER JOIN 
(
    SELECT '2016-02-23' AS start_date, 
            DATE_ADD('2016-02-23',INTERVAL 1 DAY ) AS plus_1_date,
            DATE_ADD('2016-02-23',INTERVAL 2 DAY ) AS plus_2_date,
            DATE_ADD('2016-02-23',INTERVAL 3 DAY ) AS plus_3_date,
            DATE_ADD('2016-02-23',INTERVAL 4 DAY ) AS plus_4_date,
            DATE_ADD('2016-02-23',INTERVAL 5 DAY ) AS plus_5_date,
            DATE_ADD('2016-02-23',INTERVAL 6 DAY ) AS plus_6_date,
            DATE_ADD('2016-02-23',INTERVAL 7 DAY ) AS end_date
) sub01
WHERE `date` BETWEEN start_date AND end_date
AND `team`=4
GROUP BY shift_report.advisor, 
        shift_report.team
ORDER BY percentage DESC;

Если вы просто хотите вставить 1 дату в 1 место, то примерно так:-

SELECT shift_report.advisor, 
    shift_report.team, 
    MIN(shift_report.`date`) AS week_commencing, 
    SUM(shift_report.time) as total_time, 
    ROUND(SUM(shift_report.time)/450 * 100,2) as percentage, 
    ROUND(100 * SUM(IF(shift_report.`date` = start_date, shift_report.time, 0)) / SUM(shift_report.time), 2) AS Day1,
    ROUND(100 * SUM(IF(shift_report.`date` = plus_1_date, shift_report.time, 0)) / SUM(shift_report.time), 2) AS Day2,
    ROUND(100 * SUM(IF(shift_report.`date` = plus_2_date, shift_report.time, 0)) / SUM(shift_report.time), 2) AS Day3,
    ROUND(100 * SUM(IF(shift_report.`date` = plus_3_date, shift_report.time, 0)) / SUM(shift_report.time), 2) AS Day4,
    ROUND(100 * SUM(IF(shift_report.`date` = plus_4_date, shift_report.time, 0)) / SUM(shift_report.time), 2) AS Day5,
    ROUND(100 * SUM(IF(shift_report.`date` = plus_5_date, shift_report.time, 0)) / SUM(shift_report.time), 2) AS Day6,
    ROUND(100 * SUM(IF(shift_report.`date` = plus_6_date, shift_report.time, 0)) / SUM(shift_report.time), 2) AS Day7,
    ROUND(100 * SUM(IF(shift_report.`date` = end_date, shift_report.time, 0)) / SUM(shift_report.time), 2) AS Day8
FROM shift_report
INNER JOIN 
(
    SELECT base_start_date AS start_date, 
            DATE_ADD(base_start_date,INTERVAL 1 DAY ) AS plus_1_date,
            DATE_ADD(base_start_date,INTERVAL 2 DAY ) AS plus_2_date,
            DATE_ADD(base_start_date,INTERVAL 3 DAY ) AS plus_3_date,
            DATE_ADD(base_start_date,INTERVAL 4 DAY ) AS plus_4_date,
            DATE_ADD(base_start_date,INTERVAL 5 DAY ) AS plus_5_date,
            DATE_ADD(base_start_date,INTERVAL 6 DAY ) AS plus_6_date,
            DATE_ADD(base_start_date,INTERVAL 7 DAY ) AS end_date
    FROM
    (
        SELECT '2016-02-23' AS base_start_date
    ) sub2
) sub1
WHERE `date` BETWEEN start_date AND end_date
AND `team`=4
GROUP BY shift_report.advisor, 
        shift_report.team
ORDER BY percentage DESC;

Обратите внимание, что оба из них избегают вычисления даты в предложении WHERE (что в противном случае могло бы вызвать вычисление для любой строки данных, а не только для запроса).

Answer 3

Спасибо за ответы, у меня теперь есть правильные данные, используя:

SELECT shift_report.advisor, 
shift_report.team, 
MIN(shift_report.`date`) AS week_commencing, 
SUM(shift_report.time) as total_time, 
ROUND(100 * SUM(IF(shift_report.`date` = 20160223, shift_report.time, 0)) / 450,2) AS Day1,
ROUND(100 * SUM(IF(shift_report.`date` = 20160224, shift_report.time, 0)) / 450,2) AS Day2,
ROUND(100 * SUM(IF(shift_report.`date` = 20160225, shift_report.time, 0)) / 450,2) AS Day3,
ROUND(100 * SUM(IF(shift_report.`date` = 20160226, shift_report.time, 0)) / 450,2) AS Day4,
ROUND(100 * SUM(IF(shift_report.`date` = 20160227, shift_report.time, 0)) / 450,2) AS Day5,
ROUND(100 * SUM(IF(shift_report.`date` = 20160228, shift_report.time, 0)) / 450,2) AS Day6,
ROUND(100 * SUM(IF(shift_report.`date` = 20160229, shift_report.time, 0)) / 450,2) AS Day7

ОТ shift_report ГДЕ date >=20160223 И team=4 GROUP BY shift_report.advisor ORDER BY total_time DESC;

Можно ли в любом случае использовать первый день и автоматизировать оставшиеся 6 дней на основе ввода первой даты?

Answer 4

Будет ли это работать? Это немного жестко запрограммировано, и вы можете попытаться сделать его более сложным, но это основной принцип, который вы могли бы использовать:

    SELECT advisor, team, week_commencing, total_time, 
           max(case when `date` = 20160223 then percentage end) day_1,
           max(case when `date` = 20160224 then percentage end) day_2,
           max(case when `date` = 20160225 then percentage end) day_3,
           max(case when `date` = 20160226 then percentage end) day_4,
           max(case when `date` = 20160227 then percentage end) day_5,
           max(case when `date` = 20160228 then percentage end) day_6,
           max(case when `date` = 20160229 then percentage end) day_7
    FROM (
       select distinct(shift_report.advisor) AS advisor, team,
             shift_report.date as week_commencing, SUM(shift_report.time) as    total_time, 
             round(SUM(shift_report.time)/450 * 100,2) as percentage 
       from shift_report
       where `date` >=20160223
         AND `date`<=20160301
         AND `team`=4
       GROUP BY shift_report.advisor ORDER BY percentage DESC
    ) your_query
   GROUP BY advisor, team, week_commencing, total_time;