DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `CreateTbls_recent_rainfall_by_visits`()
BEGIN
DECLARE exit handler for SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
@errno = MYSQL_ERRNO,
@text = MESSAGE_TEXT;
SET
@full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
SELECT
@full_error;
END;
/* create table of rainfall in past 24 hours for each hour for past year - used for each of the following 4 create tables */
DROP TABLE IF EXISTS last_24_hrs_rain_past_year;
CREATE TABLE last_24_hrs_rain_past_year (
updated_time DATETIME NOT NULL,
to_date DATETIME NOT NULL,
from_date DATETIME NOT NULL,
rain_in_last_24_hours FLOAT(5, 1) NULL,
cumulative_rain FLOAT(5, 1) NULL,
PRIMARY KEY (to_date)
)
SELECT
DATE_ADD(UTC_TIMESTAMP(), INTERVAL 10 HOUR) AS updated_time,
ROUND(HourlyCumulativeRain_a.cumulative_rain, 1) AS cumulative_rain,
HourlyCumulativeRain_a.date_hour AS to_date,
HourlyCumulativeRain_b.date_hour AS from_date,
ROUND(
(
HourlyCumulativeRain_a.cumulative_rain - HourlyCumulativeRain_b.cumulative_rain
),
1
) AS rain_in_last_24_hours
FROM
(
/* Query to return the cumulative rainfall for each hour for the past year plus 1 day and for the TO_DATE field of the outer query */
SELECT
hour AS date_hour,
ROUND((@CumlativeRain: = @CumlativeRain + rain_hour), 1) as cumulative_rain
FROM
(
SELECT
hour,
SUM(rain_since_midnight_hour) as rain_hour
FROM
(
SELECT
LogDateTime AS hour,
IF(
RainSinceMidnight > 0,
RainSinceMidnight - @lastValue,
0
) AS rain_since_midnight_hour,
@lastValue: = RainSinceMidnight,
@CumlativeRain: = 0
FROM
weatherdata.Monthly m,
(
SELECT
@lastValue: = 0
) SQLVars
WHERE
LogDateTime >= (CURDATE() - INTERVAL 1 YEAR - INTERVAL 1 DAY)
GROUP BY
YEAR(m.LogDateTime),
MONTH(m.LogDateTime),
DAY(m.LogDateTime),
HOUR(m.LogDateTime),
MINUTE(m.LogDateTime)
ORDER BY
(LogDateTime)
) as m1
GROUP BY
YEAR(hour),
MONTH(hour),
DAY(hour),
HOUR(hour)
) as m2
GROUP BY
hour
ORDER BY
hour
) AS HourlyCumulativeRain_a
LEFT JOIN (
/* Query to return the cumulative rainfall for each hour for the past year plus 1 day for the FROM_DATE field of the outer query */
SELECT
hour AS date_hour,
ROUND((@CumlativeRain: = @CumlativeRain + rain_hour), 1) as cumulative_rain
FROM
(
SELECT
hour,
SUM(rain_since_midnight_hour) as rain_hour
FROM
(
SELECT
LogDateTime AS hour,
IF(
RainSinceMidnight > 0,
RainSinceMidnight - @lastValue,
0
) AS rain_since_midnight_hour,
@lastValue: = RainSinceMidnight,
@CumlativeRain: = 0
FROM
weatherdata.Monthly m,
(
SELECT
@lastValue: = 0
) SQLVars
WHERE
LogDateTime >= (CURDATE() - INTERVAL 1 YEAR - INTERVAL 1 DAY)
GROUP BY
YEAR(m.LogDateTime),
MONTH(m.LogDateTime),
DAY(m.LogDateTime),
HOUR(m.LogDateTime),
MINUTE(m.LogDateTime)
ORDER BY
(LogDateTime)
) as m1
GROUP BY
YEAR(hour),
MONTH(hour),
DAY(hour),
HOUR(hour)
) as m2
GROUP BY
hour
ORDER BY
hour
) AS HourlyCumulativeRain_b ON /* Join the inner queries to find the rainfall in the previous 24 hours for each hour */
HourlyCumulativeRain_a.date_hour - INTERVAL 1 DAY = HourlyCumulativeRain_b.date_hour
WHERE
HourlyCumulativeRain_b.cumulative_rain IS NOT NULL;
/* end of create table */
-- query1 to create table -average daily rain by number of visitors
DROP TABLE IF EXISTS avg_daily_rain_by_visits;
CREATE TABLE avg_daily_rain_by_visits (
updated_time DATETIME NOT NULL,
visit_time_count SMALLINT NOT NULL,
visit_total SMALLINT NOT NULL,
rain_in_last_24_hours SMALLINT NULL,
PRIMARY KEY (rain_in_last_24_hours)
)
SELECT
updated_time,
COUNT(visit_time_hour) AS visit_time_count,
SUM(visit_count) AS visit_total,
rain_in_last_24_hours
FROM
(
SELECT
last_24_hrs_rain_past_year.updated_time,
DATE_ADD(
DATE_FORMAT(
matomo.mtys_log_link_visit_action.server_time,
"%Y-%m-%d %H"
),
INTERVAL 10 HOUR
) AS visit_time_hour,
COUNT(
DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)
) AS visit_count,
ROUND(
AVG(last_24_hrs_rain_past_year.rain_in_last_24_hours),
0
) AS rain_in_last_24_hours
FROM
matomo.mtys_log_link_visit_action
JOIN weatherdata.last_24_hrs_rain_past_year ON YEAR(
DATE_ADD(
matomo.mtys_log_link_visit_action.server_time,
INTERVAL 10 HOUR
)
) = YEAR(last_24_hrs_rain_past_year.to_date)
AND MONTH(
DATE_ADD(
matomo.mtys_log_link_visit_action.server_time,
INTERVAL 10 HOUR
)
) = MONTH(last_24_hrs_rain_past_year.to_date)
AND DAY(
DATE_ADD(
matomo.mtys_log_link_visit_action.server_time,
INTERVAL 10 HOUR
)
) = DAY(last_24_hrs_rain_past_year.to_date)
AND HOUR(
DATE_ADD(
matomo.mtys_log_link_visit_action.server_time,
INTERVAL 10 HOUR
)
) = HOUR(last_24_hrs_rain_past_year.to_date)
WHERE
matomo.mtys_log_link_visit_action.server_time >= (
weatherdata.last_24_hrs_rain_past_year.updated_time - INTERVAL 1 YEAR
)
AND last_24_hrs_rain_past_year.rain_in_last_24_hours <> 0
GROUP BY
YEAR(matomo.mtys_log_link_visit_action.server_time),
MONTH(matomo.mtys_log_link_visit_action.server_time),
DAY(matomo.mtys_log_link_visit_action.server_time)
) AS Data
GROUP BY
rain_in_last_24_hours;
/* query2 to create table - average hourly rain by number of visitors */
DROP TABLE IF EXISTS avg_hourly_rain_by_visits;
CREATE TABLE avg_hourly_rain_by_visits (
updated_time DATETIME NOT NULL,
visit_time_count SMALLINT NOT NULL,
visit_total SMALLINT NOT NULL,
rain_in_last_24_hours SMALLINT NULL,
PRIMARY KEY (rain_in_last_24_hours)
)
SELECT
updated_time,
COUNT(visit_time_hour) AS visit_time_count,
SUM(visit_count) AS visit_total,
rain_in_last_24_hours
FROM
(
SELECT
last_24_hrs_rain_past_year.updated_time,
DATE_ADD(
DATE_FORMAT(
matomo.mtys_log_link_visit_action.server_time,
"%Y-%m-%d %H"
),
INTERVAL 10 HOUR
) AS visit_time_hour,
COUNT(
DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)
) AS visit_count,
ROUND(
AVG(last_24_hrs_rain_past_year.rain_in_last_24_hours),
0
) AS rain_in_last_24_hours
FROM
matomo.mtys_log_link_visit_action
JOIN weatherdata.last_24_hrs_rain_past_year ON YEAR(
DATE_ADD(
matomo.mtys_log_link_visit_action.server_time,
INTERVAL 10 HOUR
)
) = YEAR(last_24_hrs_rain_past_year.to_date)
AND MONTH(
DATE_ADD(
matomo.mtys_log_link_visit_action.server_time,
INTERVAL 10 HOUR
)
) = MONTH(last_24_hrs_rain_past_year.to_date)
AND DAY(
DATE_ADD(
matomo.mtys_log_link_visit_action.server_time,
INTERVAL 10 HOUR
)
) = DAY(last_24_hrs_rain_past_year.to_date)
AND HOUR(
DATE_ADD(
matomo.mtys_log_link_visit_action.server_time,
INTERVAL 10 HOUR
)
) = HOUR(last_24_hrs_rain_past_year.to_date)
WHERE
matomo.mtys_log_link_visit_action.server_time >= (
weatherdata.last_24_hrs_rain_past_year.updated_time - INTERVAL 1 YEAR
)
AND last_24_hrs_rain_past_year.rain_in_last_24_hours <> 0
GROUP BY
YEAR(matomo.mtys_log_link_visit_action.server_time),
MONTH(matomo.mtys_log_link_visit_action.server_time),
DAY(matomo.mtys_log_link_visit_action.server_time),
HOUR(matomo.mtys_log_link_visit_action.server_time)
) AS Data
GROUP BY
rain_in_last_24_hours;
/* query3 to create table - grouped hourly rainfall by visitors */
DROP TABLE IF EXISTS grouped_hourly_rain_by_visits;
CREATE TABLE grouped_hourly_rain_by_visits (
updated_time DATETIME NOT NULL,
rain_in_last_24_hours_grouped VARCHAR(10),
visit_time_count SMALLINT NOT NULL,
visitor_total SMALLINT NOT NULL,
visitor_percent FLOAT(5, 4) NULL,
PRIMARY KEY (rain_in_last_24_hours_grouped)
)
SELECT
updated_time,
rain_in_last_24_hours_grouped,
visit_time_count,
visitor_total,
visitor_total / total_visitors.visitor_grand_total AS visitor_percent
FROM
/* take the visitor and recent rainfall values and bin into the standard rainfall groups */
(
SELECT
updated_time,
COUNT(visit_time_hour) AS visit_time_count,
SUM(visitor_count) AS visitor_total,
CASE
WHEN rain_in_last_24_hours = 0 THEN 0
WHEN rain_in_last_24_hours = 0.2 THEN 0.2
WHEN rain_in_last_24_hours BETWEEN 0.2
AND 1 THEN '0.4 to <1'
WHEN rain_in_last_24_hours BETWEEN 1
AND 5 THEN '1 to <5'
WHEN rain_in_last_24_hours BETWEEN 5
AND 10 THEN '5 to <10'
WHEN rain_in_last_24_hours BETWEEN 10
AND 20 THEN '10 to <20'
WHEN rain_in_last_24_hours BETWEEN 20
AND 50 THEN '20 to <50'
WHEN rain_in_last_24_hours BETWEEN 50
AND 100 THEN '50 to <100'
WHEN rain_in_last_24_hours >= 100 THEN '>=100'
ELSE ROUND(rain_in_last_24_hours, 0)
END AS rain_in_last_24_hours_grouped,
rain_in_last_24_hours
FROM
(
/* Inner query to join visitor information to rainfall in the preceding 24 hours for the past year. */
SELECT
weatherdata.last_24_hrs_rain_past_year.updated_time AS updated_time,
DATE_ADD(
DATE_FORMAT(
matomo.mtys_log_link_visit_action.server_time,
"%Y-%m-%d %H"
),
INTERVAL 10 HOUR
) AS visit_time_hour,
COUNT(
DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)
) AS visitor_count,
last_24_hrs_rain_past_year.rain_in_last_24_hours
FROM
matomo.mtys_log_link_visit_action
LEFT JOIN weatherdata.last_24_hrs_rain_past_year ON YEAR(
DATE_ADD(
matomo.mtys_log_link_visit_action.server_time,
INTERVAL 10 HOUR
)
) = YEAR(last_24_hrs_rain_past_year.to_date)
AND MONTH(
DATE_ADD(
matomo.mtys_log_link_visit_action.server_time,
INTERVAL 10 HOUR
)
) = MONTH(last_24_hrs_rain_past_year.to_date)
AND DAY(
DATE_ADD(
matomo.mtys_log_link_visit_action.server_time,
INTERVAL 10 HOUR
)
) = DAY(last_24_hrs_rain_past_year.to_date)
AND HOUR(
DATE_ADD(
matomo.mtys_log_link_visit_action.server_time,
INTERVAL 10 HOUR
)
) = HOUR(last_24_hrs_rain_past_year.to_date)
WHERE
matomo.mtys_log_link_visit_action.server_time >= (
weatherdata.last_24_hrs_rain_past_year.updated_time - INTERVAL 1 YEAR
)
GROUP BY
YEAR(matomo.mtys_log_link_visit_action.server_time),
MONTH(matomo.mtys_log_link_visit_action.server_time),
DAY(matomo.mtys_log_link_visit_action.server_time),
HOUR(matomo.mtys_log_link_visit_action.server_time)
) AS Data1
GROUP BY
rain_in_last_24_hours_grouped
) AS Data2 /* Cross Join query to find the total number of visits. This is used in the outer SELECT to calculate the % distribution of visits by grouped rainfall amounts */
CROSS JOIN (
SELECT
SUM(visitor_count) AS visitor_grand_total
FROM
(
SELECT
COUNT(
DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)
) AS visitor_count
FROM
matomo.mtys_log_link_visit_action
LEFT JOIN weatherdata.last_24_hrs_rain_past_year ON YEAR(
DATE_ADD(
matomo.mtys_log_link_visit_action.server_time,
INTERVAL 10 HOUR
)
) = YEAR(last_24_hrs_rain_past_year.to_date)
AND MONTH(
DATE_ADD(
matomo.mtys_log_link_visit_action.server_time,
INTERVAL 10 HOUR
)
) = MONTH(last_24_hrs_rain_past_year.to_date)
AND DAY(
DATE_ADD(
matomo.mtys_log_link_visit_action.server_time,
INTERVAL 10 HOUR
)
) = DAY(last_24_hrs_rain_past_year.to_date)
AND HOUR(
DATE_ADD(
matomo.mtys_log_link_visit_action.server_time,
INTERVAL 10 HOUR
)
) = HOUR(last_24_hrs_rain_past_year.to_date)
WHERE
matomo.mtys_log_link_visit_action.server_time >= (
weatherdata.last_24_hrs_rain_past_year.updated_time - INTERVAL 1 YEAR
)
GROUP BY
YEAR(matomo.mtys_log_link_visit_action.server_time),
MONTH(matomo.mtys_log_link_visit_action.server_time),
DAY(matomo.mtys_log_link_visit_action.server_time),
HOUR(matomo.mtys_log_link_visit_action.server_time)
) AS Data
) as total_visitors
GROUP BY
rain_in_last_24_hours_grouped
ORDER BY
rain_in_last_24_hours;
/* query4 to create table - visitor engagement by grouped daily rainfall */
/* Query to return the % visitors engagement by rainfall amount joining weatherdata to analytics data */
DROP TABLE IF EXISTS visit_activity_by_grouped_daily_rain;
CREATE TABLE visit_activity_by_grouped_daily_rain (
updated_time DATETIME NOT NULL,
rain_in_last_24_hours_grouped VARCHAR(10) NOT NULL,
rain_in_last_24_hours_count SMALLINT NOT NULL,
visit_time_count SMALLINT NULL,
visitor_total SMALLINT NOT NULL,
visitor_percent_of_grouped_rain FLOAT(5, 4) NOT NULL,
PRIMARY KEY (rain_in_last_24_hours_grouped)
)
SELECT
updated_time,
rain_in_last_24_hours_grouped,
rain_in_last_24_hours_count,
visit_time_count,
visitor_total,
ROUND(visit_time_count / rain_in_last_24_hours_count, 3) AS visitor_percent_of_grouped_rain
FROM
/* take the visitor and recent rainfall values and bin into the standard rainfall groups */
(
SELECT
updated_time,
COUNT(rain_time_hour) AS rain_in_last_24_hours_count,
COUNT(visit_time_hour) AS visit_time_count,
SUM(visitor_count) AS visitor_total,
IF(
rain_in_last_24_hours = 0,
0,
IF(
rain_in_last_24_hours = 0.2,
0.2,
IF(
rain_in_last_24_hours BETWEEN 0.2
AND 1,
'0.4 to <1',
IF(
rain_in_last_24_hours BETWEEN 1
AND 5,
'1 to <5',
IF(
rain_in_last_24_hours BETWEEN 5
AND 10,
'5 to <10',
IF(
rain_in_last_24_hours BETWEEN 10
AND 20,
'10 to <20',
IF(
rain_in_last_24_hours BETWEEN 20
AND 50,
'20 to <50',
IF(
rain_in_last_24_hours BETWEEN 50
AND 100,
'50 to <100',
IF(
rain_in_last_24_hours BETWEEN 100
AND 1000,
'>100',
ROUND(rain_in_last_24_hours, 0)
)
)
)
)
)
)
)
)
) as rain_in_last_24_hours_grouped,
rain_in_last_24_hours
FROM
(
/* Inner query to join visitor information to average rainfall grouped by day by rainfall in the preceding 24 hours for the past year */
SELECT
last_24_hrs_rain_past_year.updated_time AS updated_time,
DATE_ADD(
DATE_FORMAT(
last_24_hrs_rain_past_year.to_date,
"%Y-%m-%d %H"
),
INTERVAL 10 HOUR
) AS rain_time_hour,
DATE_ADD(
DATE_FORMAT(
matomo.mtys_log_link_visit_action.server_time,
"%Y-%m-%d %H"
),
INTERVAL 10 HOUR
) AS visit_time_hour,
CASE
WHEN COUNT(
DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)
) >= 1 THEN COUNT(
DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)
)
ELSE 0
END AS visitor_count,
AVG(
weatherdata.last_24_hrs_rain_past_year.rain_in_last_24_hours
) AS rain_in_last_24_hours
FROM
matomo.mtys_log_link_visit_action
RIGHT JOIN weatherdata.last_24_hrs_rain_past_year ON YEAR(
DATE_ADD(
matomo.mtys_log_link_visit_action.server_time,
INTERVAL 10 HOUR
)
) = YEAR(last_24_hrs_rain_past_year.to_date)
AND MONTH(
DATE_ADD(
matomo.mtys_log_link_visit_action.server_time,
INTERVAL 10 HOUR
)
) = MONTH(last_24_hrs_rain_past_year.to_date)
AND DAY(
DATE_ADD(
matomo.mtys_log_link_visit_action.server_time,
INTERVAL 10 HOUR
)
) = DAY(last_24_hrs_rain_past_year.to_date)
AND HOUR(
DATE_ADD(
matomo.mtys_log_link_visit_action.server_time,
INTERVAL 10 HOUR
)
) = HOUR(last_24_hrs_rain_past_year.to_date)
WHERE
last_24_hrs_rain_past_year.to_date >= (CURDATE() - INTERVAL 1 YEAR)
GROUP BY
YEAR(last_24_hrs_rain_past_year.to_date),
MONTH(last_24_hrs_rain_past_year.to_date),
DAY(last_24_hrs_rain_past_year.to_date)
) AS Data
GROUP BY
rain_in_last_24_hours_grouped
) AS DATA
GROUP BY
rain_in_last_24_hours_grouped
ORDER BY
rain_in_last_24_hours;
/* finally delete the rainfall table initially created as we don't need that data now */
DROP TABLE IF EXISTS last_24_hrs_rain_past_year;
END$$
DELIMITER ;