1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
|
import clickhouse from '@/lib/clickhouse';
import { EVENT_COLUMNS, FILTER_COLUMNS, SESSION_COLUMNS } from '@/lib/constants';
import { CLICKHOUSE, PRISMA, runQuery } from '@/lib/db';
import prisma from '@/lib/prisma';
import type { QueryFilters } from '@/lib/types';
const FUNCTION_NAME = 'getSessionMetrics';
export interface SessionMetricsParameters {
type: string;
limit?: number | string;
offset?: number | string;
}
export async function getSessionMetrics(
...args: [websiteId: string, parameters: SessionMetricsParameters, filters: QueryFilters]
) {
return runQuery({
[PRISMA]: () => relationalQuery(...args),
[CLICKHOUSE]: () => clickhouseQuery(...args),
});
}
async function relationalQuery(
websiteId: string,
parameters: SessionMetricsParameters,
filters: QueryFilters,
) {
const { type, limit = 500, offset = 0 } = parameters;
let column = FILTER_COLUMNS[type] || type;
const { parseFilters, rawQuery } = prisma;
const { filterQuery, joinSessionQuery, cohortQuery, queryParams } = parseFilters(
{
...filters,
websiteId,
},
{
joinSession: SESSION_COLUMNS.includes(type),
},
);
const includeCountry = column === 'city' || column === 'region';
if (type === 'language') {
column = `lower(left(${type}, 2))`;
}
return rawQuery(
`
select
${column} x,
count(distinct website_event.session_id) y
${includeCountry ? ', country' : ''}
from website_event
${cohortQuery}
${joinSessionQuery}
where website_event.website_id = {{websiteId::uuid}}
and website_event.created_at between {{startDate}} and {{endDate}}
and website_event.event_type != 2
${filterQuery}
group by 1
${includeCountry ? ', 3' : ''}
order by 2 desc
limit ${limit}
offset ${offset}
`,
{ ...queryParams, ...parameters },
FUNCTION_NAME,
);
}
async function clickhouseQuery(
websiteId: string,
parameters: SessionMetricsParameters,
filters: QueryFilters,
): Promise<{ x: string; y: number }[]> {
const { type, limit = 500, offset = 0 } = parameters;
let column = FILTER_COLUMNS[type] || type;
const { parseFilters, rawQuery } = clickhouse;
const { filterQuery, cohortQuery, queryParams } = parseFilters({
...filters,
websiteId,
});
const includeCountry = column === 'city' || column === 'region';
if (type === 'language') {
column = `lower(left(${type}, 2))`;
}
let sql = '';
if (EVENT_COLUMNS.some(item => Object.keys(filters).includes(item))) {
sql = `
select
${column} x,
count(distinct session_id) y
${includeCountry ? ', country' : ''}
from website_event
${cohortQuery}
where website_id = {websiteId:UUID}
and created_at between {startDate:DateTime64} and {endDate:DateTime64}
and event_type != 2
${filterQuery}
group by x
${includeCountry ? ', country' : ''}
order by y desc
limit ${limit}
offset ${offset}
`;
} else {
sql = `
select
${column} x,
uniq(session_id) y
${includeCountry ? ', country' : ''}
from website_event_stats_hourly as website_event
${cohortQuery}
where website_id = {websiteId:UUID}
and created_at between {startDate:DateTime64} and {endDate:DateTime64}
and event_type != 2
${filterQuery}
group by x
${includeCountry ? ', country' : ''}
order by y desc
limit ${limit}
offset ${offset}
`;
}
return rawQuery(sql, { ...queryParams, ...parameters }, FUNCTION_NAME);
}
|