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
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
|
# name: test/sql/window/test_over_grouping.test_slow
# description: Grouping OVER clauses to reduce unnecessary sorting
# group: [window]
statement ok
PRAGMA threads=4
statement ok
PRAGMA verify_parallelism
statement ok
PRAGMA enable_profiling
statement ok
PRAGMA profiling_output='__TEST_DIR__/test.txt'
statement ok
PRAGMA profiling_mode = detailed
statement ok
create table image (
id smallint primary key,
width int not null,
height integer not null
);
statement ok
insert into image (id, width, height) values (1, 500, 297);
set seed 0.8675309
statement ok
create table pixel (
image_id integer not null,
x integer not null,
y integer not null,
red utinyint not null,
green utinyint not null,
blue utinyint not null
);
statement ok
insert into pixel
select
1 as image_id,
r % 500 as x,
r // 500 as y,
random() * 255 as red,
random() * 255 as green,
random() * 255 as blue
from (select range r from range(0, 297 * 500)) r;
query I
select count(*) from pixel;
----
148500
statement ok
create temp table delta1 as select range delta from range(-1,2);
statement ok
create temp table delta2 as select x.delta as dx, y.delta as dy from delta1 x, delta1 y;
statement ok
create sequence patchids;
statement ok
create table patch AS
SELECT p.* FROM (
SELECT
nextval('patchids') AS id,
1 AS params_id,
image_id,
x + dx AS x_pos,
y + dy AS y_pos,
AVG(red) AS red_avg,
AVG(green) AS green_avg,
AVG(blue) AS blue_avg
FROM pixel, delta2
GROUP BY params_id, image_id, x_pos, y_pos
) p, image i
WHERE x_pos >= 1 AND x_pos < i.width - 1
AND y_pos >= 1 AND y_pos < i.height - 1;
query I
select count(*) from patch;
----
146910
statement ok
create temp table channel (channel char(1));
statement ok
insert into channel (channel) values ('R'), ('G'), ('B');
query I
SELECT COUNT(*)
FROM (
SELECT
patch_id,
channel,
coalesce(sqrt(grad_x * grad_x + grad_y * grad_y), 0.) AS grad_mag,
coalesce(atan2(grad_y, grad_x), 0.) AS grad_angle
FROM (
SELECT
patch_id,
channel,
(case channel when 'R' then r_x when 'G' then g_x else b_x end) as grad_x,
(case channel when 'R' then r_y when 'G' then g_y else b_y end) as grad_y
FROM (
SELECT
patch_id,
(r_x_1::integer - r_x_0::integer) / 2.0 as r_x,
(r_y_1::integer - r_y_0::integer) / 2.0 as r_y,
(g_x_1::integer - g_x_0::integer) / 2.0 as g_x,
(g_y_1::integer - g_y_0::integer) / 2.0 as g_y,
(b_x_1::integer - b_x_0::integer) / 2.0 as b_x,
(b_y_1::integer - b_y_0::integer) / 2.0 as b_y
FROM (
SELECT
px.*,
lead(red, 1) OVER (w) AS r_x_1,
lag(red, 1) OVER (w) AS r_x_0,
lead(green, 1) OVER (w) AS g_x_1,
lag(green, 1) OVER (w) AS g_x_0,
lead(blue, 1) OVER (w) AS b_x_1,
lag(blue, 1) OVER (w) AS b_x_0,
lead(red, 3) OVER (w) AS r_y_1,
lag(red, 3) OVER (w) AS r_y_0,
lead(green, 3) OVER (w) AS g_y_1,
lag(green, 3) OVER (w) AS g_y_0,
lead(blue, 3) OVER (w) AS b_y_1,
lag(blue, 3) OVER (w) AS b_y_0
FROM (
SELECT
p.id AS patch_id,
px.*
FROM (SELECT x + dx as x_pos, y + dy as y_pos, px.* FROM pixel px, delta2 d) px, patch p
WHERE px.x_pos = p.x_pos AND px.y_pos = p.y_pos
AND px.image_id = p.image_id
AND p.params_id = 1
) px
WINDOW w AS (PARTITION BY patch_id ORDER BY y, x)
) g
WHERE x_pos = x AND y_pos = y
) g, channel c
) g
) f;
----
440730
|
