TD2_DEV51_Qualite_Algo/ping-report/res/ping-report-db.sql

59 lines
1.4 KiB
MySQL
Raw Permalink Normal View History

2024-09-16 20:38:18 +02:00
CREATE TABLE HourlyReport(
ping_max FLOAT NOT NULL,
ping_min FLOAT NOT NULL,
ping_mean FLOAT NOT NULL,
nb_ping_high INT NOT NULL,
nb_ping_loss INT NOT NULL,
nb_ping_reached INT NOT NULL,
report_day DATE NOT NULL,
report_hour INT NOT NULL
);
CREATE VIEW DailyReport AS
SELECT
H.report_day,
MAX(H.ping_max) AS daily_worst_ping,
MIN(H.ping_min) AS daily_best_ping,
AVG(H.ping_mean) AS daily_mean_ping,
SUM(H.nb_ping_high) AS daily_high_ping,
SUM(H.nb_ping_loss) AS daily_loss_ping,
SUM(H.nb_ping_reached) AS daily_reached_ping,
(
SELECT
SH.report_hour
FROM
HourlyReport SH
WHERE
SH.ping_mean = (
SELECT
MIN(SSH.ping_mean)
FROM
HourlyReport SSH
WHERE
SSH.report_day = sh.report_day
)
AND
SH.report_day = H.report_day
) daily_best_hour,
(
SELECT
SH.report_hour
FROM
HourlyReport SH
WHERE
SH.ping_mean = (
SELECT
MAX(SSH.ping_mean)
FROM
HourlyReport SSH
WHERE
SSH.report_day = sh.report_day
)
AND
SH.report_day = H.report_day
) daily_worst_hour
FROM
HourlyReport H
GROUP BY
report_day