uVH4EwVN

1--postgresql 11.2. Implementation-specific functions would need to be translated to use with a different flavor of SQL.
2--psql terminal workflow
3
4--create table. only needs done the first time, hence "if not exists" clause
5create table if not exists client_table (
6	person_id varchar(50),
7	start_time bigint,
8	duration_one integer,
9	duration_two bigint,
10	error_one smallint,
11	parameters text,
12	session_id text primary key,
13	error_one_list text,
14	error_two integer,
15	error_two_list text
16	);
17
18-- import file into table (can be done with GUI). need psql meta-command \copy instead of sql copy
19-- b/c copy doesn't have access to client filesystem
20-- will append data from file to end of table
21\copy client_table
22	from '~/path/input.csv' delimiter ',' csv header; --update file name and path
23
24-- main query
25select 
26	person_id, 
27	sum(
28		case when (error_one > 0 and duration_two = 0) then 1
29		else 0
30		end) as error_one,
31	(sum(
32		case when (error_one > 0 and duration_two = 0) then 1
33		else 0
34		end) / 1.0 / count(*) * 100)::decimal(5,2) as error_one_pct,
35	string_agg(distinct error_one_list, ', ') filter(where error_one_list <> '') as error_one_list, -- provide distinct errors only. commas also occur organically in error code text—consider a different separator
36	(sum(
37		case when (error_one > 0 or duration_two > 0) then 0
38		else 1
39		end) / 1.0 / count(*) * 100)::decimal(5,2) as exit_pct,
40	sum(error_two) as error_two,
41	(sum(error_two) / 1.0 /
42	nullif((sum( -- nullif necessary to prevent div/0 error
43		case when (duration_one = -1) then 0 
44		else 1 
45		end)),0) * 100)::decimal(5,2) as error_two_pct,
46	string_agg(distinct error_two_list, ', ') filter(where error_two_list <> '') as error_two_list -- provide distinct errors only. commas also occur organically in error code text—consider a different separator
47	from client_table
48	where start_time >= extract(epoch from timestamptz '3/25/19 00:00:00-07') -- Update the date to match that of your file. Leave timestamp and timezone alone.
49		and start_time < (extract(epoch from timestamptz '3/25/19 00:00:00-07') + 86400) -- 86400 seconds in a day
50		and parameters like '%parameter.name=fake+pattern%'
51	group by person_id;
52
53-- create function to allow easier management. Assumes uploading multiple files to one database table.
54-- View makes less sense b/c we want to change the date and pattern values
55create or replace function fatal_errors_per_user_per_day(input_date text, fake_pattern text)
56	returns table (person_id text, error_one bigint, error_one_pct decimal(5,2), error_one_list text, exit_pct decimal(5,2), error_two bigint, error_two_pct decimal(5,2), error_two_list text)
57	as
58	$body$
59	select 
60	person_id, 
61	sum(
62		case when (error_one > 0 and duration_two = 0) then 1
63		else 0
64		end) as error_one,
65	(sum(
66		case when (error_one > 0 and duration_two = 0) then 1
67		else 0
68		end) / 1.0 / count(*) * 100)::decimal(5,2) as error_one_pct,
69	string_agg(distinct error_one_list, ', ') filter(where error_one_list <> '') as error_one_list,
70	(sum(
71		case when (error_one > 0 or duration_two > 0) then 0
72		else 1
73		end) / 1.0 / count(*) * 100)::decimal(5,2) as exit_pct,
74	sum(error_two) as error_two,
75	(sum(error_two) / 1.0 /
76	nullif((sum( -- nullif necessary to prevent div/0 error
77		case when (duration_one = -1) then 0 
78		else 1
79		end)),0) * 100)::decimal(5,2) as error_two_pct,
80	string_agg(distinct error_two_list, ', ') filter(where error_two_list <> '') as error_two_list
81	from client_table
82	where start_time >= extract(epoch from timestamptz ($1 || ' 00:00:00-07'))
83		and start_time < (extract(epoch from timestamptz ($1 || ' 00:00:00-07')) + 86400) -- 86400 seconds in a day
84		and parameters like ('%parameter.name=' || $2 || '%')
85	group by person_id;
86	$body$
87	language sql;
88
89-- how to use function:
90select * from
91fatal_errors_per_user_per_day('3/25/19', 'fake_pattern') -- specify date and pattern
92
93-- create temp table and insert query result into it in preparation for export
94drop table if exists client_temp; -- want to work with a clean slate for each file export
95
96create table client_temp (
97	person_id text,
98	error_one bigint,
99	error_one_pct decimal(5,2),
100	error_one_list text,
101	exit_pct decimal(5,2),
102	error_two bigint,
103	error_two_pct decimal(5,2),
104	error_two_list text
105	);
106
107insert into client_temp
108	select * from
109	fatal_errors_per_user_per_day('3/25/19', 'fake_pattern');
110
111-- export temp table to csv if you wish to analyze elsewhere
112\copy client_temp to '~/path/output.csv' delimiter ',' csv header;