Форматирование данных JSON по месяцам для диаграмм высокого уровня с использованием MySQL

Question

Форматирование данных JSON по месяцам для диаграмм высокого уровня с использованием MySQL

Есть две таблицы. Первая таблица показывает мне количество беременных женщин, находящихся под наблюдением одной медсестры. Имейте в виду, что есть много медсестер, которые подчиняются одному врачу. Каждая беременная женщина привязана к одной медсестре, а каждая медсестра привязана к одному врачу. Таким образом, иерархия

Беременные Женщины--> Медсестра--> Доктор.

Теперь у каждой беременной женщины есть дата регистрации.

Вторая таблица показывает мне сопоставление медсестер с врачи.

Теперь мой запрос должен получить подробную информацию о врачах и количестве зарегистрированных беременных женщин по месяцам (с января по декабрь) для каждого врача. Поэтому мой конечный результат должен быть в формате JSON, как показано ниже. Этот формат мне нужен для хайчартов.

И если в течение определенного месяца не было беременных женщин, он должен вернуть 0

[{
        name: 'Doctor ID',
        data: [7.0(Jan), 6.9(Feb), 9.5, 14.5, 18.2, 21.5, 25.2, 26.5, 23.3, 18.3,   13.9, 9.6(Dec)],

       name: 'Doctor ID 2',
        data: [-0.2, 0.8, 5.7, 11.3, 17.0, 22.0, 24.8, 24.1, 20.1, 14.1, 8.6, 2.5]
    }, {
        name: 'Doctor Id 3',
         data: [-0.9, 0.6, 3.5, 8.4, 13.5, 17.0, 18.6, 17.9, 14.3, 9.0, 3.9, 1.0]
    }]

Я попытался выполнить следующий запрос, но я не могу создать структуру, когда дело доходит до Месяцы.

SELECT preg_reg_other.preg_id,nurse_dets.doctor_id,preg_reg_other.reg_date
from preg_reg_other,nurse_dets
where preg_reg_other.nurse_id = nurse_dets.nurse_id
and preg_reg_other.reg_date is not null
and nurse_dets.doctor_id= 1031
order by preg_reg_other.reg_date asc

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

Две таблицы выглядят следующим образом

Таблица 1: nurse_dets

nurse_id  population  doctor_id nurse_name

Таблица 2: preg_reg_other

preg_id  nurse_id  reg_date

5

php json mysql sql highcharts

Author: Clarion Smith, 2015-05-25

Source

1 answers

score 2 · Accepted Answer

Я бы сделал так:

Для каждого врача сделайте запрос следующим образом:

/* get all pregrant women for a doctor, group by month and count records */
SELECT MONTH( p.reg_date ), COUNT(*) 
FROM nurse_dets n
INNER JOIN preg_reg_other p 
    ON n.nurse_id = p.nurse_id 
WHERE n.doctor_id = 1301
AND n.reg_date IS NOT NULL
GROUP BY MONTH( p.reg_date )
ORDER BY p.reg_date ASC

Это даст вам за каждый месяц количество беременных женщин, связанных с врачом с идентификатором 1301

Теперь вы можете выполнить подобный запрос для каждого врача и на каждой итерации создавать для вас другую серию диаграмм

В конце концов вы передадите весь свой массив рядов своему клиенту и получите данные для highcharts

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

Получение значения значение 0 для месяцев, в которых нет беременных женщин, немного сложнее, так как группа by не возвращает никакого значения, если нет хотя бы одной записи для подсчета. Попробуйте сделать это следующим образом:

SELECT months.num, COUNT( p.preg_id )
FROM 
   ( SELECT 1 AS num
     UNION ALL SELECT 2 
     UNION ALL SELECT 3 
     UNION ALL SELECT 4 
     UNION ALL SELECT 5  
     UNION ALL SELECT 6 
     UNION ALL SELECT 7  
     UNION ALL SELECT 8 
     UNION ALL SELECT 9  
     UNION ALL SELECT 10
     UNION ALL SELECT 11  
     UNION ALL SELECT 12 ) months
LEFT JOIN preg_reg_other p 
    ON months.num = MONTH ( p.reg_date )
INNER JOIN nurse_dets n
    ON n.nurse_id = p.nurse_id
WHERE n.doctor_id = 1301    
GROUP BY MONTH( months.num )
ORDER BY months.num ASC

Сначала мы вручную создаем таблицу с 12 месяцами и "соединяем слева" с другими таблицами, поэтому, если записи нет, итоговый подсчет должен привести к 0

Я не проверял запрос, но надеюсь, вы поняли идею