Bell Curves - Museums - All Times

PHOTO EMBED

Tue Jan 31 2023 17:20:10 GMT+0000 (Coordinated Universal Time)

Saved by @seckin #sql

--all times - all museums

select 
	
	--distinct tablewithcounts.project_name
	tablewithcounts.freq_val, count(tablewithcounts.id)
	

--tablewithcounts.id, tablewithcounts.project_name, tablewithcounts.metric_name, tablewithcounts."time", tablewithcounts."Day", tablewithcounts."Hour", tablewithcounts.value, tablewithcounts.freq_val, tablewithcounts.display_value --, geometry_id

from
(
SELECT
	id, project_name, metric_name, "time", value, display_value, geometry_id,
	round(cast(value as numeric), 1) as freq_val,
	extract(hour from "time") as "Hour",
	to_char("time", 'Day') as "Day"
	FROM public.metrics_app_timeandgeometric_historic
	--where project_name in ('Lasswade')
	where 
	(
	project_name = 'Roman_Baths'
	--or project_name = 'Mauricewood_School'
	--or project_name = 'Eaglesham_Primaryschool'
	or
	project_name in (
			'Roman_Baths'
			)
	)
	and metric_name = 'temp'
	and (value >= 0 and value < 50)
	and display_value is NULL
) as tablewithcounts	


-- where 
-- --tablewithcounts."Day" not in ('Saturday ', 'Sunday   ')
-- tablewithcounts."Day" not like '%Saturday%'
-- and tablewithcounts."Day" not like '%Sunday%'
-- and tablewithcounts."Hour" >= 8 and tablewithcounts."Hour" < 16

-- and tablewithcounts.time not between '2021-08-01' and '2021-08-17'
-- and tablewithcounts.time not between '2021-09-20' and '2021-09-20'
-- and tablewithcounts.time not between '2021-10-18' and '2021-10-25'
-- and tablewithcounts.time not between '2021-12-23' and '2022-01-10'
-- and tablewithcounts.time not between '2022-02-14' and '2022-02-18'
-- and tablewithcounts.time not between '2022-04-11' and '2022-04-22'
-- and tablewithcounts.time not between '2022-05-02' and '2022-05-02'
-- and tablewithcounts.time not between '2022-05-23' and '2022-05-23'
-- and tablewithcounts.time not between '2022-07-01' and '2022-07-31'
-- and tablewithcounts.time not between '2022-08-01' and '2022-08-16'
-- and tablewithcounts.time not between '2022-09-16' and '2022-09-19'
-- and tablewithcounts.time not between '2022-10-17' and '2022-10-24'
-- and tablewithcounts.time not between '2022-12-21' and '2023-01-04'
-- and tablewithcounts.time not between '2023-02-13' and '2023-02-17'
-- and tablewithcounts.time not between '2023-04-03' and '2023-04-14'
-- and tablewithcounts.time not between '2023-05-01' and '2023-05-01'
-- and tablewithcounts.time not between '2023-05-22' and '2023-05-22'
-- and tablewithcounts.time not between '2023-06-29' and '2023-07-31'


group by tablewithcounts.freq_val
order by tablewithcounts.freq_val

--limit 1000
content_copyCOPY