更新:我已经更新了测试字符串,以弥补我遗漏的一个 case .
我正在try 使用正则表达式计算查询中的WHERE
个过滤器的数量.
因此,通常的 idea 是计算查询中出现的WHERE
和AND
的数量,而不包括发生在JOIN
之后和WHERE
之前的AND
.也不包括第CASE WHEN
条中出现的AND
条.
例如,此查询:
WITH cte AS (\nSELECT a,b\nFROM something\nWHERE a>10\n AND b<5)\n, cte2 AS (\n SELECT c,\nd FROM another\nWHERE c>10\nAND d<5)\n SELECT CASE WHEN c1.a=1\nAND c2.c=1 THEN 'yes' ELSE 'no' \nEND,c1.a,c1.b,c2.c,c2.d\nFROM cte c1\nINNER JOIN cte2 c2 ON c1.a = c2.c\nAND c1.b = c2.d\nWHERE c1.a<4 AND DATE(c1)>'2022-01-01'\nAND c2.c>6
-- FORMATTED FOR EASE OF READ. PLEASE USE LINE ABOVE AS REGEX TEST STRING
WITH cte AS (
SELECT a,b
FROM something
WHERE a>10
AND b<5
)
, cte2 AS (
SELECT c,d
FROM another
WHERE c>10
AND d<5
)
SELECT
CASE
WHEN c1.a=1 AND c2.c=1 THEN 'yes'
WHEN c1.a=1 AND c2.c=1 THEN 'maybe'
ELSE 'no'
END,
c1.a,
c1.b,
c2.c,
c2.d
FROM cte c1
INNER JOIN cte2 c2
ON c1.a = c2.c
AND c1.b = c2.d
WHERE c1.a<4
AND DATE(c1)>'2022-01-01'
AND c2.c>6
应返回7
,它们是:
-
WHERE a>10
个 -
AND b<5
个 -
WHERE c>10
个 -
AND d<5
个 -
WHERE c1.a<4
个 -
AND DATE(c1)>'2022-01-01'
个 -
AND c2.c>6
个
不计算部分AND c1.b = c2.d
,因为它发生在JOIN
之后,WHERE
之前.
部分AND c2.c=1
不计入,因为它在CASE WHEN
条款中.
我最终计划在一个PostgreSQL查询上使用它来统计在特定时间段内所有查询中发生的过滤器的数量.
我试着四处寻找答案,我自己也试过了,但都没有结果.因此在这里寻求帮助.提前谢谢你了!