135 lines
6.3 KiB
SQL
135 lines
6.3 KiB
SQL
-- View: public.scanning_report_daily_full
|
|
|
|
-- DROP VIEW public.scanning_report_daily_full;
|
|
|
|
CREATE OR REPLACE VIEW public.scanning_report_daily_full
|
|
AS
|
|
WITH daily AS (
|
|
SELECT cagg_scanning_report_daily."CreationDate",
|
|
cagg_scanning_report_daily."LocalDate",
|
|
cagg_scanning_report_daily."ClientMac",
|
|
cagg_scanning_report_daily."NetworkId",
|
|
cagg_scanning_report_daily."NearestApMac",
|
|
cagg_scanning_report_daily."Manufacturer",
|
|
cagg_scanning_report_daily."OS",
|
|
cagg_scanning_report_daily."SSID",
|
|
cagg_scanning_report_daily."MinimumRssi",
|
|
cagg_scanning_report_daily."MaximumRssi",
|
|
cagg_scanning_report_daily."AverageRssi",
|
|
cagg_scanning_report_daily."DurationInMinutes",
|
|
cagg_scanning_report_daily."FirstDetection",
|
|
cagg_scanning_report_daily."LastDetection",
|
|
cagg_scanning_report_daily."LocalFirstDetection",
|
|
cagg_scanning_report_daily."LocalLastDetection",
|
|
cagg_scanning_report_daily."DetectionsCount",
|
|
cagg_scanning_report_daily."PersonType",
|
|
cagg_scanning_report_daily."PresenceCategory",
|
|
cagg_scanning_report_daily."HourlyPeriodsDetected"
|
|
FROM cagg_scanning_report_daily
|
|
WHERE cagg_scanning_report_daily."CreationDate" < CURRENT_DATE
|
|
UNION ALL
|
|
SELECT scanning_report_daily."CreationDate",
|
|
scanning_report_daily."LocalDate",
|
|
scanning_report_daily."ClientMac",
|
|
scanning_report_daily."NetworkId",
|
|
scanning_report_daily."NearestApMac",
|
|
scanning_report_daily."Manufacturer",
|
|
scanning_report_daily."OS",
|
|
scanning_report_daily."SSID",
|
|
scanning_report_daily."MinimumRssi",
|
|
scanning_report_daily."MaximumRssi",
|
|
scanning_report_daily."AverageRssi",
|
|
scanning_report_daily."DurationInMinutes",
|
|
scanning_report_daily."FirstDetection",
|
|
scanning_report_daily."LastDetection",
|
|
scanning_report_daily."LocalFirstDetection",
|
|
scanning_report_daily."LocalLastDetection",
|
|
scanning_report_daily."DetectionsCount",
|
|
scanning_report_daily."PersonType",
|
|
scanning_report_daily."PresenceCategory",
|
|
scanning_report_daily."HourlyPeriodsDetected"
|
|
FROM scanning_report_daily
|
|
WHERE scanning_report_daily."CreationDate" >= CURRENT_DATE
|
|
), enriched_data AS (
|
|
SELECT daily."CreationDate",
|
|
daily."LocalDate",
|
|
daily."ClientMac",
|
|
daily."NetworkId",
|
|
daily."NearestApMac",
|
|
daily."Manufacturer",
|
|
daily."OS",
|
|
daily."SSID",
|
|
daily."MinimumRssi",
|
|
daily."MaximumRssi",
|
|
daily."AverageRssi",
|
|
daily."DurationInMinutes",
|
|
daily."FirstDetection",
|
|
daily."LastDetection",
|
|
daily."LocalFirstDetection",
|
|
daily."LocalLastDetection",
|
|
daily."DetectionsCount",
|
|
daily."PersonType",
|
|
daily."PresenceCategory",
|
|
daily."HourlyPeriodsDetected",
|
|
CASE
|
|
WHEN uc."MacAddress" IS NOT NULL THEN true
|
|
ELSE false
|
|
END AS "IsRegisteredUser",
|
|
net."Name" AS "NetworkName",
|
|
org."Name" AS "OrganizationName",
|
|
ap."Name" AS "AccessPointName",
|
|
ap."Model" AS "AccessPointModel",
|
|
ap."Latitude",
|
|
ap."Longitude"
|
|
FROM daily
|
|
LEFT JOIN ( SELECT DISTINCT "SplashUserConnections"."MacAddress"
|
|
FROM "SplashUserConnections"
|
|
WHERE "SplashUserConnections"."IsDeleted" = false) uc ON daily."ClientMac"::text = uc."MacAddress"
|
|
LEFT JOIN "SplashMerakiNetworks" net ON daily."NetworkId" = net."Id" AND net."IsDeleted" = false
|
|
LEFT JOIN "SplashMerakiOrganizations" org ON net."OrganizationId" = org."Id"
|
|
LEFT JOIN "SplashAccessPoints" ap ON daily."NearestApMac" = ap."Mac"
|
|
)
|
|
SELECT "ClientMac" AS "MacAddress",
|
|
"LocalDate" AS "DetectionDate",
|
|
"FirstDetection",
|
|
"LastDetection",
|
|
EXTRACT(dow FROM (("FirstDetection" AT TIME ZONE 'UTC'::text) AT TIME ZONE 'America/Mexico_City'::text)) AS "DayOfWeek",
|
|
to_char((("FirstDetection" AT TIME ZONE 'UTC'::text) AT TIME ZONE 'America/Mexico_City'::text), 'Day'::text) AS "DayName",
|
|
EXTRACT(day FROM (("FirstDetection" AT TIME ZONE 'UTC'::text) AT TIME ZONE 'America/Mexico_City'::text)) AS "DayNumber",
|
|
EXTRACT(week FROM (("FirstDetection" AT TIME ZONE 'UTC'::text) AT TIME ZONE 'America/Mexico_City'::text)) AS "WeekNumber",
|
|
EXTRACT(month FROM (("FirstDetection" AT TIME ZONE 'UTC'::text) AT TIME ZONE 'America/Mexico_City'::text)) AS "MonthNumber",
|
|
to_char((("FirstDetection" AT TIME ZONE 'UTC'::text) AT TIME ZONE 'America/Mexico_City'::text), 'Month'::text) AS "MonthName",
|
|
EXTRACT(year FROM (("FirstDetection" AT TIME ZONE 'UTC'::text) AT TIME ZONE 'America/Mexico_City'::text)) AS "Year",
|
|
"PersonType",
|
|
CASE
|
|
WHEN "DurationInMinutes" < 1::numeric THEN 'Less than 1 minute'::text
|
|
WHEN "DurationInMinutes" >= 1::numeric AND "DurationInMinutes" <= 5::numeric THEN '1-5 minutes'::text
|
|
WHEN "DurationInMinutes" >= 5::numeric AND "DurationInMinutes" <= 15::numeric THEN '5-15 minutes'::text
|
|
WHEN "DurationInMinutes" >= 15::numeric AND "DurationInMinutes" <= 30::numeric THEN '15-30 minutes'::text
|
|
WHEN "DurationInMinutes" >= 30::numeric AND "DurationInMinutes" <= 60::numeric THEN '30-60 minutes'::text
|
|
WHEN "DurationInMinutes" >= 60::numeric AND "DurationInMinutes" <= 120::numeric THEN '1-2 horas'::text
|
|
WHEN "DurationInMinutes" > 120::numeric THEN 'More than 2 hours'::text
|
|
ELSE NULL::text
|
|
END AS "PresenceCategory",
|
|
"DurationInMinutes",
|
|
"DetectionsCount",
|
|
"IsRegisteredUser",
|
|
"MinimumRssi",
|
|
"AverageRssi",
|
|
"MaximumRssi",
|
|
"Manufacturer",
|
|
"OS",
|
|
"SSID",
|
|
"NetworkName",
|
|
"OrganizationName",
|
|
"AccessPointName",
|
|
"AccessPointModel",
|
|
"Latitude",
|
|
"Longitude",
|
|
"NetworkId"
|
|
FROM enriched_data
|
|
WHERE "DurationInMinutes" > 1::numeric;
|
|
|
|
ALTER TABLE public.scanning_report_daily_full
|
|
OWNER TO mysql;
|