blib/lib/Database/BI/Insights.pm

filter: Statementstmt 100% 302 / 302 Branchbran 86.8% 132 / 152 Conditioncond 73.0% 46 / 63 MC/DCmcdc 47.2% 17 / 36 Subroutinesub 100% 21 / 21 Podpod 100% 2 / 2 totaltotal 90.2% 520 / 576 CC 120 SCAR 28.3
CC
120
Cov
93%
_find_outliers
33.5
_top_movers
24.0
compute
16.0
CRAP 16.9
« blib/lib/Database/BI/Command/password.pm ↑ Summary blib/lib/Database/BI/Model/Geocode.pm »
1 package Database::BI::Insights;
2  
3 1 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
4 1 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
5  
6 1 our $VERSION = '0.023.1';
7  
8 1 ▶use Database::BI::Util qw(urlencode);
Subroutine BEGIN: called CC=1 SCAR=0.0
9 1 ▶use List::Util qw(sum min max);
Subroutine BEGIN: called CC=1 SCAR=0.0
10 1 ▶use Scalar::Util qw(looks_like_number);
Subroutine BEGIN: called CC=1 SCAR=0.0
11 1 ▶use Readonly;
Subroutine BEGIN: called CC=1 SCAR=0.0
12  
13 # ---------------------------------------------------------------------------
14 # Constants
15 # ---------------------------------------------------------------------------
16  
17 # IQR fence multiplier (Tukey, 1977).
18 1 Readonly my $IQR_FENCE => 1.5;
19  
20 # Minimum distinct periods required for period-over-period and seasonality.
21 1 Readonly my $MIN_PERIODS_POP => 2;
22 1 Readonly my $MIN_PERIODS_SEASON => 8;
23  
24 # Minimum rows in a group to attempt outlier detection.
25 1 Readonly my $MIN_ROWS_OUTLIER => 4;
26  
27 # Autocorrelation threshold above which we call a pattern "seasonal".
28 1 Readonly my $SEASON_R_THRESHOLD => 0.6;
29  
30 # Maximum outliers reported per column (keeps the panel readable).
31 1 Readonly my $MAX_OUTLIERS_PER_COL => 5;
32  
33 # Maximum top-mover rows reported.
34 1 Readonly my $MAX_TOP_MOVERS => 5;
35  
36 # ---------------------------------------------------------------------------
37 # Public API
38 # ---------------------------------------------------------------------------
39  
40 # compute( records => \@rows, columns => \@cols, spec_key => $l, base_url => $url )
41 # -> \@insights
42 #
43 # Each insight hashref has:
44 # kind -- 'outlier' | 'top_mover' | 'period_delta' | 'seasonality'
45 # headline -- one short sentence
46 # detail -- one sentence giving the numbers
47 # view_url -- absolute-path URL (without host) linking to the view that shows it
48 # (always present; the spec says "an insight you cannot click through is a bug")
49 # method -- key into %METHOD_NOTES documenting the algorithm used
50 #
51 # Arguments:
52 # records -- arrayref of row hashrefs (pre-filtered, post-calc)
53 # columns -- arrayref of column names in order
54 # spec_key -- the l= value (e.g. "table:sales" or "path:/data/x.csv")
55 # base_url -- base request URL to build view_url from (e.g. "/stats?l=table:sales")
56 #
57 # NOTE: This sub is pure arithmetic -- no LLM, no external calls.
58 49 ▶sub compute {
Subroutine compute: called CC=16 SCAR=27.7
Pod documented
59 49 my (%args) = @_;
60 49 ▶ my $records = $args{records} // [];
Condition100%2/2
$args{"records"} // []
l!l
481
MC/DC100%1/1
$args{"records"} // []
$args{"records"}
Truth table100%2/2
A
A= $args{"records"}
Aresult
11
00
61 49 ▶ my $columns = $args{columns} // [];
Condition100%2/2
$args{"columns"} // []
l!l
481
MC/DC100%1/1
$args{"columns"} // []
$args{"columns"}
Truth table100%2/2
A
A= $args{"columns"}
Aresult
11
00
62 49 ▶ my $spec_key = $args{spec_key} // '';
Condition100%2/2
$args{"spec_key"} // ""
l!l
445
MC/DC100%1/1
$args{"spec_key"} // ""
$args{"spec_key"}
Truth table100%2/2
A
A= $args{"spec_key"}
Aresult
11
00
63 49 ▶ my $base_url = $args{base_url} // "/insights?l=$spec_key";
Condition100%2/2
$args{"base_url"} // "/insights?l=$spec_key"
l!l
409
MC/DC100%1/1
$args{"base_url"} // "/insights?l=$spec_key"
$args{"base_url"}
Truth table100%2/2
A
A= $args{"base_url"}
Aresult
11
00
64  
65 49 ▶ return [] unless @$records && @$columns;
Branch: 1 branch
BranchTrueFalseTotal
unless @$records and @$columns 3 46 49
Condition100%3/3
@$records and @$columns
!ll&&!rl&&r
2146
MC/DC100%2/2
@$records and @$columns
@$records @$columns
Truth table100%3/3
A and B
A= @$recordsB= @$columns
ABresult
0X0
100
111
66  
67 46 my @insights;
68  
69 # Identify date columns (ISO-8601 YYYY-MM-DD) and numeric columns.
70 46 my (@date_cols, @numeric_cols);
71 46 for my $col (@$columns) {
72 257 ▶ my @vals = grep { defined $_ && length $_ } map { $_->{$col} } @$records;
Condition100%3/3
defined $_ && length $_
!ll&&!rl&&r
111559
MC/DC100%2/2
defined $_ && length $_
defined $_ length $_
Truth table100%3/3
A && B
A= defined $_B= length $_
ABresult
0X0
100
111
73 257 ▶ next unless @vals;
Branch: 1 branch
BranchTrueFalseTotal
unless @vals 1 256 257
74 256 ▶ if (_is_date_col(\@vals)) {
Branch: 2 branches
BranchTrueFalseTotal
if (_is_date_col(\@vals)) { } 44 212 256
elsif (_is_numeric_col(\@vals)) { } 85 127 212
75 44 push @date_cols, $col;
76 } elsif (_is_numeric_col(\@vals)) {
77 85 push @numeric_cols, $col;
78 }
79 }
80  
81 # Outlier detection: for each numeric column scan all distinct category
82 # groups formed by each non-numeric, non-date column. When the dataset
83 # has no categorical column, treat all rows as one group.
84 my @cat_cols = grep {
85 46 my $c = $_;
86 257 !grep { $_ eq $c } (@date_cols, @numeric_cols)
87 } @$columns;
88  
89 46 for my $num_col (@numeric_cols) {
90 85 my @found = _find_outliers($records, $num_col, \@cat_cols, $spec_key, $base_url);
91 85 push @insights, @found;
92 }
93  
94 # Period-over-period and top movers: need a date column + at least one
95 # numeric column.
96 46 ▶ if (@date_cols && @numeric_cols) {
Branch: 1 branch
BranchTrueFalseTotal
if (@date_cols and @numeric_cols) 44 2 46
Condition66%2/3
@date_cols and @numeric_cols
!ll&&!rl&&r
2044
MC/DC50%1/2
@date_cols and @numeric_cols
@date_cols @numeric_cols
Truth table66%2/3
A and B
A= @date_colsB= @numeric_cols
ABresult
0X0
100
111
97 44 my $date_col = $date_cols[0]; # use the first date column found
98 44 for my $num_col (@numeric_cols) {
99 85 push @insights,
100 _period_over_period($records, $date_col, $num_col, $spec_key, $base_url);
101 85 push @insights,
102 _top_movers($records, $date_col, $num_col, \@cat_cols, $spec_key, $base_url);
103 85 push @insights,
104 _seasonality($records, $date_col, $num_col, $spec_key, $base_url);
105 }
106 }
107  
108 46 return \@insights;
109 }
110  
111 # method_note($method_key) -> $string
112 #
113 # Returns a one-sentence human-readable description of the algorithm behind
114 # a given insight method key. Used by the template's tooltip/footnote.
115 6 ▶sub method_note {
Subroutine method_note: called CC=2 SCAR=7.0
Pod documented
116 6 my $key = shift;
117 6 my %notes = (
118 iqr_fence => 'Outlier: value beyond 1.5 x IQR from Q1 or Q3 (Tukey fence).',
119 pop_delta => 'Period-over-period: sum for the most-recent period vs. the prior period.',
120 top_mover => 'Top mover: dimension member with the largest period-over-period absolute change.',
121 autocorr => 'Seasonality: lag-1 autocorrelation on period sums; r >= 0.6 flagged.',
122 );
123 6 ▶ return $notes{$key} // $key;
Condition66%2/3
$notes{$key} // $key
l!l&&r!l&&!r
510
MC/DC0%0/2
$notes{$key} // $key
$notes{$key} $key
Truth table66%2/3
A // B
A= $notes{$key}B= $key
ABresult
1X1
011
000
124 }
125  
126 # ---------------------------------------------------------------------------
127 # Private helpers
128 # ---------------------------------------------------------------------------
129  
130 # _looks_like_date($v) -> bool
131 #
132 # Returns true when $v matches any common date format. Checked BEFORE numeric
133 # classification so that slash-dates (e.g. 12/22/2025 from US bank exports),
134 # EU slash-dates (22/12/2025), and dash-Mon dates (22-Oct-2026) are never
135 # stripped of their punctuation and misread as large integers like 12222025.
136 1174 ▶sub _looks_like_date {
Subroutine _looks_like_date: called CC=6 SCAR=17.9
137 1174 my $v = shift;
138 1174 ▶ return 0 unless defined $v && length $v;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $v and length $v 2 1172 1174
Condition100%3/3
defined $v and length $v
!ll&&!rl&&r
111172
MC/DC100%2/2
defined $v and length $v
defined $v length $v
Truth table100%3/3
A and B
A= defined $vB= length $v
ABresult
0X0
100
111
139 1172 ▶ return 1 if $v =~ /\A\d{4}-\d{2}-\d{2}\z/; # ISO-8601
Branch: 1 branch
BranchTrueFalseTotal
if $v =~ /\A\d{4}-\d{2}-\d{2}\z/ 297 875 1172
140 875 ▶ return 1 if $v =~ /\A\d{1,2}\/\d{1,2}\/\d{4}\z/; # M/D/YYYY or D/M/YYYY
Branch: 1 branch
BranchTrueFalseTotal
if $v =~ m[\A\d{1,2}/\d{1,2}/\d{4}\z] 3 872 875
141 872 ▶ return 1 if $v =~ /\A\d{1,2}-[A-Za-z]{3}-\d{4}\z/; # DD-Mon-YYYY
Branch: 1 branch
BranchTrueFalseTotal
if $v =~ /\A\d{1,2}-[A-Za-z]{3}-\d{4}\z/ 1 871 872
142 871 return 0;
143 }
144  
145 260 ▶sub _is_date_col {
Subroutine _is_date_col: called CC=5 SCAR=16.1
146 260 my $vals = shift;
147 260 ▶ my $n = @$vals > 20 ? 20 : scalar @$vals;
Branch: 1 branch
BranchTrueFalseTotal
@$vals > 20 ? : 1 259 260
148 260 my $ok = 0;
149 260 for my $v (@{$vals}[0 .. $n - 1]) {
150 509 ▶ return 0 unless _looks_like_date($v);
Branch: 1 branch
BranchTrueFalseTotal
unless _looks_like_date($v) 213 296 509
151 296 $ok++;
152 }
153 47 ▶ return $ok ? 1 : 0;
Branch: 1 branch
BranchTrueFalseTotal
$ok ? : 46 1 47
154 }
155  
156 216 ▶sub _is_numeric_col {
Subroutine _is_numeric_col: called CC=7 SCAR=19.5
157 216 my $vals = shift;
158 216 ▶ return 0 unless @$vals;
Branch: 1 branch
BranchTrueFalseTotal
unless @$vals 1 215 216
159 215 my $ok = 0;
160 215 ▶ my $n = @$vals > 20 ? 20 : scalar @$vals;
Branch: 1 branch
BranchTrueFalseTotal
@$vals > 20 ? : 0 215 215
161 215 for my $v (@{$vals}[0 .. $n - 1]) {
162 652 ▶ return 0 if _looks_like_date($v); # date values are not numeric measures
Branch: 1 branch
BranchTrueFalseTotal
if _looks_like_date($v) 1 651 652
163 651 my $num = _to_num($v);
164 651 ▶ return 0 unless defined $num;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $num 128 523 651
165 523 $ok++;
166 }
167 86 ▶ return $ok ? 1 : 0;
Branch: 1 branch
BranchTrueFalseTotal
$ok ? : 86 0 86
168 }
169  
170 3134 ▶sub _to_num {
Subroutine _to_num: called CC=1 SCAR=0.0
171 3134 return Database::BI::Util::parse_number($_[0]);
172 }
173  
174 # _quartiles(\@sorted_nums) -> ($q1, $median, $q3)
175 #
176 # Uses the "inclusive" method: for n values, Q1 is the median of the lower
177 # half (not including the overall median when n is odd), Q3 the median of the
178 # upper half. This matches R's type=2 and Excel's QUARTILE.INC.
179 17 ▶sub _quartiles {
Subroutine _quartiles: called CC=3 SCAR=11.0
180 17 my $s = shift; # already sorted
181 17 my $n = scalar @$s;
182 17 ▶ return (undef, undef, undef) unless $n >= 4;
Branch: 1 branch
BranchTrueFalseTotal
unless $n >= 4 2 15 17
183  
184 15 my $mid = int($n / 2);
185 15 my @lo = @{$s}[0 .. $mid - 1];
186 15 ▶ my @hi = @{$s}[($n % 2 ? $mid + 1 : $mid) .. $n - 1];
Branch: 1 branch
BranchTrueFalseTotal
$n % 2 ? : 13 2 15
187  
188 15 my $q1 = _median(\@lo);
189 15 my $q2 = _median($s);
190 15 my $q3 = _median(\@hi);
191 15 return ($q1, $q2, $q3);
192 }
193  
194 49 ▶sub _median {
Subroutine _median: called CC=3 SCAR=11.0
195 49 my $s = shift;
196 49 my $n = scalar @$s;
197 49 ▶ return undef unless $n;
Branch: 1 branch
BranchTrueFalseTotal
unless $n 1 48 49
198 48 my $m = int($n / 2);
199 48 ▶ return $n % 2 ? $s->[$m] : ($s->[$m - 1] + $s->[$m]) / 2;
Branch: 1 branch
BranchTrueFalseTotal
$n % 2 ? : 19 29 48
200 }
201  
202 # _find_outliers($records, $num_col, \@cat_cols, $spec_key, $base_url) -> @insights
203 #
204 # For each categorical group (or all rows when @cat_cols is empty), apply the
205 # Tukey IQR fence to the numeric column. Report up to MAX_OUTLIERS_PER_COL
206 # values that fall outside [Q1 - 1.5*IQR, Q3 + 1.5*IQR].
207 96 ▶sub _find_outliers {
Subroutine _find_outliers: called CC=32 SCAR=35.1
208 96 my ($records, $num_col, $cat_cols, $spec_key, $base_url) = @_;
209  
210 96 my @insights;
211  
212 # Group records by category key (empty string when no cat_cols).
213 96 my (%groups, %group_example);
214 96 for my $row (@$records) {
215 663 ▶ my $key = join("\x00", map { $row->{$_} // '' } @$cat_cols);
Condition50%1/2
$$row{$_} // ""
l!l
15320
MC/DC0%0/1
$$row{$_} // ""
$$row{$_}
Truth table50%1/2
A
A= $$row{$_}
Aresult
11
00
216 663 my $val = _to_num($row->{$num_col});
217 663 ▶ push @{ $groups{$key} }, $val if defined $val;
Branch: 1 branch
BranchTrueFalseTotal
if defined $val 654 9 663
218 663 ▶ $group_example{$key} //= $row;
Condition66%2/3
$group_example{$key} //= $row
l!l&&r!l&&!r
1575060
MC/DC0%0/2
$group_example{$key} //= $row
$group_example{$key} $row
Truth table66%2/3
A //= B
A= $group_example{$key}B= $row
ABresult
1X1
011
000
219 }
220  
221 96 for my $key (sort keys %groups) {
222 505 my @nums = @{ $groups{$key} };
223 505 ▶ next if @nums < $MIN_ROWS_OUTLIER;
Branch: 1 branch
BranchTrueFalseTotal
if @nums < $MIN_ROWS_OUTLIER 494 11 505
224  
225 11 my @sorted = sort { $a <=> $b } @nums;
226 11 my ($q1, undef, $q3) = _quartiles(\@sorted);
227 11 ▶ next unless defined $q1 && defined $q3;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $q1 and defined $q3 0 11 11
Condition33%1/3
defined $q1 and defined $q3
!ll&&!rl&&r
0011
MC/DC0%0/2
defined $q1 and defined $q3
defined $q1 defined $q3
Truth table33%1/3
A and B
A= defined $q1B= defined $q3
ABresult
0X0
100
111
228  
229 11 my $iqr = $q3 - $q1;
230 11 my $lo_fence = $q1 - $IQR_FENCE * $iqr;
231 11 my $hi_fence = $q3 + $IQR_FENCE * $iqr;
232  
233 # Find outlier rows.
234 11 my @outlier_rows;
235 11 for my $row (@$records) {
236 159 ▶ my $gkey = join("\x00", map { $row->{$_} // '' } @$cat_cols);
Condition50%1/2
$$row{$_} // ""
l!l
560
MC/DC0%0/1
$$row{$_} // ""
$$row{$_}
Truth table50%1/2
A
A= $$row{$_}
Aresult
11
00
237 159 ▶ next unless $gkey eq $key;
Branch: 1 branch
BranchTrueFalseTotal
unless $gkey eq $key 3 156 159
238 156 my $val = _to_num($row->{$num_col});
239 156 ▶ next unless defined $val;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $val 0 156 156
240 156 ▶ next unless $val < $lo_fence || $val > $hi_fence;
Branch: 1 branch
BranchTrueFalseTotal
unless $val < $lo_fence or $val > $hi_fence 141 15 156
Condition100%3/3
$val < $lo_fence or $val > $hi_fence
l!l&&r!l&&!r
114141
MC/DC100%2/2
$val < $lo_fence or $val > $hi_fence
$val < $lo_fence $val > $hi_fence
Truth table100%3/3
A or B
A= $val < $lo_fenceB= $val > $hi_fence
ABresult
1X1
011
000
241 15 push @outlier_rows, { val => $val, row => $row };
242 }
243 11 ▶ next unless @outlier_rows;
Branch: 1 branch
BranchTrueFalseTotal
unless @outlier_rows 2 9 11
244  
245 # Sort by distance from nearest fence, descending.
246 @outlier_rows = sort {
247 9 _fence_distance($b->{val}, $lo_fence, $hi_fence) <=>
248 9 _fence_distance($a->{val}, $lo_fence, $hi_fence)
249 } @outlier_rows;
250  
251 9 ▶ splice @outlier_rows, $MAX_OUTLIERS_PER_COL if @outlier_rows > $MAX_OUTLIERS_PER_COL;
Branch: 1 branch
BranchTrueFalseTotal
if @outlier_rows > $MAX_OUTLIERS_PER_COL 1 8 9
252  
253 # Build one insight per outlier.
254 9 for my $o (@outlier_rows) {
255 13 my $val = $o->{val};
256 13 my $row = $o->{row};
257  
258 # Build a human-readable group label.
259 13 my $group_label = '';
260 13 ▶ if (@$cat_cols) {
Branch: 1 branch
BranchTrueFalseTotal
if (@$cat_cols) 5 8 13
261 5 my @parts;
262 5 for my $c (@$cat_cols) {
263 5 ▶ push @parts, "$c=" . ($row->{$c} // '');
Condition50%1/2
$$row{$c} // ""
l!l
50
MC/DC0%0/1
$$row{$c} // ""
$$row{$c}
Truth table50%1/2
A
A= $$row{$c}
Aresult
11
00
264 }
265 5 $group_label = join(', ', @parts);
266 }
267  
268 13 ▶ my $dir = $val > $hi_fence ? 'high' : 'low';
Branch: 1 branch
BranchTrueFalseTotal
$val > $hi_fence ? : 12 1 13
269 13 ▶ my $headline = $group_label
Branch: 1 branch
BranchTrueFalseTotal
$group_label ? : 5 8 13
270 ? "Unusual $num_col in $group_label: " . _fmt_num($val) . " is unusually $dir"
271 : "Unusual $num_col value: " . _fmt_num($val) . " is unusually $dir";
272  
273 13 my $detail = sprintf(
274 "Expected range %s \x{2013} %s (IQR fence on %d values)",
275 _fmt_num($lo_fence), _fmt_num($hi_fence), scalar @nums
276 );
277  
278 # Build view_url: link to /bar for cat_cols, /stats otherwise.
279 13 my $view_url;
280 13 ▶ if (@$cat_cols) {
Branch: 1 branch
BranchTrueFalseTotal
if (@$cat_cols) { } 5 8 13
281 5 my $cat = urlencode($cat_cols->[0]);
282 5 my $val_enc = urlencode($num_col);
283 5 $view_url = "/bar?l=${\urlencode($spec_key)}&cat=$cat&val=$val_enc&sort=value";
284 } else {
285 8 $view_url = "/stats?l=" . urlencode($spec_key);
286 }
287  
288 # Build drill_url: data table filtered to category group so the
289 # outlier row is visible in context of its peers. The hl_col /
290 # hl_op / hl_val params tell dashboard.html.tt to highlight and
291 # scroll to the specific outlier row(s) within those 75+ peers.
292 13 my $drill_url;
293 13 ▶ if (@$cat_cols) {
Branch: 1 branch
BranchTrueFalseTotal
if (@$cat_cols) 5 8 13
294 5 my $base = _spec_to_data_url($spec_key);
295 5 ▶ if ($base) {
Branch: 1 branch
BranchTrueFalseTotal
if ($base) 5 0 5
296 5 my $cat_enc = urlencode($cat_cols->[0]);
297 5 ▶ my $gval = (split /\x00/, $key)[0] // '';
Condition50%1/2
(split(/\x00/, $key, 0))[0] // ""
l!l
50
MC/DC0%0/1
(split(/\x00/, $key, 0))[0] // ""
(split(/\x00/, $key, 0))[0]
Truth table50%1/2
A
A= (split(/\x00/, $key, 0))[0]
Aresult
11
00
298 5 my $gv_enc = urlencode($gval);
299 5 ▶ my $sep = ($base =~ /\?/) ? '&' : '?';
Branch: 1 branch
BranchTrueFalseTotal
$base =~ /\?/ ? : 0 5 5
300 5 $drill_url = $base . $sep . 'f=' . $cat_enc . ':eq:' . $gv_enc;
301 # Highlight the outlier row(s): beyond the IQR fence.
302 5 ▶ my $hl_op = ($val > $hi_fence) ? 'gt' : 'lt';
Branch: 1 branch
BranchTrueFalseTotal
$val > $hi_fence ? : 5 0 5
303 5 ▶ my $hl_fence = ($val > $hi_fence) ? $hi_fence : $lo_fence;
Branch: 1 branch
BranchTrueFalseTotal
$val > $hi_fence ? : 5 0 5
304 5 $drill_url .= '&hl_col=' . urlencode($num_col)
305 . '&hl_op=' . $hl_op
306 . '&hl_val=' . urlencode(sprintf('%.10g', $hl_fence));
307 5 ▶ if ($base_url) {
Branch: 1 branch
BranchTrueFalseTotal
if ($base_url) 5 0 5
308 5 $drill_url .= '&back2=' . urlencode($base_url)
309 . '&back2_label=Back+to+insights';
310 }
311 }
312 }
313  
314 13 ▶ push @insights, {
Branch: 1 branch
BranchTrueFalseTotal
$drill_url ? : 5 8 13
315 kind => 'outlier',
316 headline => $headline,
317 detail => $detail,
318 view_url => $view_url,
319 ($drill_url ? (drill_url => $drill_url) : ()),
320 method => 'iqr_fence',
321 };
322 }
323 }
324  
325 96 return @insights;
326 }
327  
328 22 ▶sub _fence_distance {
Subroutine _fence_distance: called CC=3 SCAR=11.0
329 22 my ($val, $lo, $hi) = @_;
330 22 ▶ return $val > $hi ? $val - $hi
Branch: 2 branches
BranchTrueFalseTotal
$val > $hi ? : 19 3 22
$val < $lo ? : 1 2 3
331 : $val < $lo ? $lo - $val
332 : 0;
333 }
334  
335 # _period_over_period($records, $date_col, $num_col, $spec_key, $base_url) -> @insights
336 #
337 # Aggregates $num_col by year-month period, then reports the delta between the
338 # most recent two periods.
339 92 ▶sub _period_over_period {
Subroutine _period_over_period: called CC=14 SCAR=26.4
340 92 my ($records, $date_col, $num_col, $spec_key, $base_url) = @_;
341  
342 92 my %period_totals;
343 92 for my $row (@$records) {
344 532 ▶ my $date = $row->{$date_col} // '';
Condition50%1/2
$$row{$date_col} // ""
l!l
5320
MC/DC0%0/1
$$row{$date_col} // ""
$$row{$date_col}
Truth table50%1/2
A
A= $$row{$date_col}
Aresult
11
00
345 532 ▶ next unless $date =~ /\A(\d{4}-\d{2})/;
Branch: 1 branch
BranchTrueFalseTotal
unless $date =~ /\A(\d{4}-\d{2})/ 2 530 532
346 530 my $period = $1;
347 530 my $val = _to_num($row->{$num_col});
348 530 ▶ next unless defined $val;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $val 2 528 530
349 528 $period_totals{$period} += $val;
350 }
351  
352 92 my @periods = sort keys %period_totals;
353 92 ▶ return () unless @periods >= $MIN_PERIODS_POP;
Branch: 1 branch
BranchTrueFalseTotal
unless @periods >= $MIN_PERIODS_POP 86 6 92
354  
355 6 my $cur = $periods[-1];
356 6 my $prev = $periods[-2];
357 6 my $cur_val = $period_totals{$cur};
358 6 my $prev_val = $period_totals{$prev};
359  
360 6 my $delta = $cur_val - $prev_val;
361 6 ▶ my $pct = $prev_val != 0 ? $delta / abs($prev_val) * 100 : undef;
Branch: 1 branch
BranchTrueFalseTotal
$prev_val != 0 ? : 5 1 6
362  
363 6 ▶ my $dir = $delta >= 0 ? 'up' : 'down';
Branch: 1 branch
BranchTrueFalseTotal
$delta >= 0 ? : 5 1 6
364 6 ▶ my $sign = $delta >= 0 ? '+' : '';
Branch: 1 branch
BranchTrueFalseTotal
$delta >= 0 ? : 5 1 6
365  
366 6 my $headline = sprintf('%s is %s %s vs prior period (%s)',
367 $num_col, $dir, _fmt_num(abs($delta)), $prev);
368  
369 6 ▶ my $detail = defined $pct
Branch: 1 branch
BranchTrueFalseTotal
defined $pct ? : 5 1 6
370 ? sprintf('%s: %s %s: %s (%s%s%%)',
371 $prev, _fmt_num($prev_val), $cur, _fmt_num($cur_val),
372 $sign, sprintf('%.1f', $pct))
373 : sprintf('%s: %s %s: %s',
374 $prev, _fmt_num($prev_val), $cur, _fmt_num($cur_val));
375  
376 6 my $enc_spec = urlencode($spec_key);
377 6 my $enc_date = urlencode($date_col);
378 6 my $enc_num = urlencode($num_col);
379 6 my $view_url = "/graph?l=$enc_spec&x=$enc_date&y=$enc_num";
380  
381 # Drill into the data table filtered to the current period.
382 6 my $drill_url;
383 6 my $base = _spec_to_data_url($spec_key);
384 6 ▶ if ($base) {
Branch: 1 branch
BranchTrueFalseTotal
if ($base) 5 1 6
385 5 ▶ my $sep = ($base =~ /\?/) ? '&' : '?';
Branch: 1 branch
BranchTrueFalseTotal
$base =~ /\?/ ? : 0 5 5
386 5 $drill_url = $base . $sep
387 . 'f=' . $enc_date . ':starts:' . urlencode($cur);
388 5 ▶ if ($base_url) {
Branch: 1 branch
BranchTrueFalseTotal
if ($base_url) 5 0 5
389 5 $drill_url .= '&back2=' . urlencode($base_url)
390 . '&back2_label=Back+to+insights';
391 }
392 }
393  
394 return ({
395 6 ▶ kind => 'period_delta',
Branch: 1 branch
BranchTrueFalseTotal
$drill_url ? : 5 1 6
396 headline => $headline,
397 detail => $detail,
398 view_url => $view_url,
399 ($drill_url ? (drill_url => $drill_url) : ()),
400 method => 'pop_delta',
401 });
402 }
403  
404 # _top_movers($records, $date_col, $num_col, \@cat_cols, $spec_key, $base_url) -> @insights
405 #
406 # For each categorical group, compute period-over-period delta between the two
407 # most recent periods, and report the top MAX_TOP_MOVERS movers.
408 89 ▶sub _top_movers {
Subroutine _top_movers: called CC=23 SCAR=31.8
409 89 my ($records, $date_col, $num_col, $cat_cols, $spec_key, $base_url) = @_;
410 89 ▶ return () unless @$cat_cols;
Branch: 1 branch
BranchTrueFalseTotal
unless @$cat_cols 1 88 89
411  
412 # Collect totals keyed by (period, group_key).
413 88 my (%totals, %group_labels);
414 88 my %all_periods;
415 88 for my $row (@$records) {
416 549 ▶ my $date = $row->{$date_col} // '';
Condition50%1/2
$$row{$date_col} // ""
l!l
5490
MC/DC0%0/1
$$row{$date_col} // ""
$$row{$date_col}
Truth table50%1/2
A
A= $$row{$date_col}
Aresult
11
00
417 549 ▶ next unless $date =~ /\A(\d{4}-\d{2})/;
Branch: 1 branch
BranchTrueFalseTotal
unless $date =~ /\A(\d{4}-\d{2})/ 1 548 549
418 548 my $period = $1;
419 548 $all_periods{$period}++;
420 548 ▶ my $gkey = join("\x00", map { $row->{$_} // '' } @$cat_cols);
Condition50%1/2
$$row{$_} // ""
l!l
15320
MC/DC0%0/1
$$row{$_} // ""
$$row{$_}
Truth table50%1/2
A
A= $$row{$_}
Aresult
11
00
421 548 my $val = _to_num($row->{$num_col});
422 548 ▶ next unless defined $val;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $val 0 548 548
423 548 $totals{$period}{$gkey} += $val;
424 548 ▶ $group_labels{$gkey} //= join(', ', map { $row->{$_} // '' } @$cat_cols);
Condition75%3/4
$group_labels{$gkey} //= join(", ", map({$$row{$_} // "";} @$cat_cols))
l!l
38510
$$row{$_} // ""
l!l
14940
MC/DC100%1/1
$group_labels{$gkey} //= join(", ", map({$$row{$_} // "";} @$cat_cols))
$group_labels{$gkey}
MC/DC0%0/1
$$row{$_} // ""
$$row{$_}
Truth table100%2/2
A
A= $group_labels{$gkey}
Aresult
11
00
Truth table50%1/2
A
A= $$row{$_}
Aresult
11
00
425 }
426  
427 88 my @periods = sort keys %all_periods;
428 88 ▶ return () unless @periods >= $MIN_PERIODS_POP;
Branch: 1 branch
BranchTrueFalseTotal
unless @periods >= $MIN_PERIODS_POP 84 4 88
429  
430 4 my $cur = $periods[-1];
431 4 my $prev = $periods[-2];
432  
433 4 my (@movers);
434 4 for my $gkey (keys %group_labels) {
435 17 ▶ my $cur_val = $totals{$cur}{$gkey} // 0;
Condition50%1/2
$totals{$cur}{$gkey} // 0
l!l
170
MC/DC0%0/1
$totals{$cur}{$gkey} // 0
$totals{$cur}{$gkey}
Truth table50%1/2
A
A= $totals{$cur}{$gkey}
Aresult
11
00
436 17 ▶ my $prev_val = $totals{$prev}{$gkey} // 0;
Condition100%2/2
$totals{$prev}{$gkey} // 0
l!l
161
MC/DC100%1/1
$totals{$prev}{$gkey} // 0
$totals{$prev}{$gkey}
Truth table100%2/2
A
A= $totals{$prev}{$gkey}
Aresult
11
00
437 17 my $delta = $cur_val - $prev_val;
438 17 push @movers, { gkey => $gkey, label => $group_labels{$gkey},
439 delta => $delta, cur => $cur_val, prev => $prev_val };
440 }
441  
442 4 ▶ return () unless @movers;
Branch: 1 branch
BranchTrueFalseTotal
unless @movers 0 4 4
443  
444 # Sort by absolute delta descending.
445 4 @movers = sort { abs($b->{delta}) <=> abs($a->{delta}) } @movers;
446 4 ▶ splice @movers, $MAX_TOP_MOVERS if @movers > $MAX_TOP_MOVERS;
Branch: 1 branch
BranchTrueFalseTotal
if @movers > $MAX_TOP_MOVERS 1 3 4
447  
448 4 my $cat_col = $cat_cols->[0];
449 4 my $enc_spec = urlencode($spec_key);
450 4 my $enc_date = urlencode($date_col);
451 4 my $enc_num = urlencode($num_col);
452 4 my $enc_cat = urlencode($cat_col);
453  
454 4 my @insights;
455 4 for my $m (@movers) {
456 10 ▶ my $dir = $m->{delta} >= 0 ? 'up' : 'down';
Branch: 1 branch
BranchTrueFalseTotal
$$m{'delta'} >= 0 ? : 9 1 10
457 10 ▶ my $sign = $m->{delta} >= 0 ? '+' : '';
Branch: 1 branch
BranchTrueFalseTotal
$$m{'delta'} >= 0 ? : 9 1 10
458 my $headline = sprintf('%s %s %s by %s from %s to %s',
459 10 $m->{label}, $num_col, $dir, _fmt_num(abs($m->{delta})), $prev, $cur);
460 my $detail = sprintf('%s: %s %s: %s',
461 10 $prev, _fmt_num($m->{prev}), $cur, _fmt_num($m->{cur}));
462  
463 # Encode the group value for the filter.
464 10 ▶ my $gval_enc = urlencode((split /\x00/, $m->{gkey})[0] // '');
Condition50%1/2
(split(/\x00/, $$m{"gkey"}, 0))[0] // ""
l!l
100
MC/DC0%0/1
(split(/\x00/, $$m{"gkey"}, 0))[0] // ""
(split(/\x00/, $$m{"gkey"}, 0))[0]
Truth table50%1/2
A
A= (split(/\x00/, $$m{"gkey"}, 0))[0]
Aresult
11
00
465 10 my $view_url = "/graph?l=$enc_spec&x=$enc_date&y=$enc_num"
466 . "&f=$enc_cat:eq:$gval_enc";
467  
468 # Drill into data table filtered to category + current period.
469 10 my $drill_url;
470 10 my $base = _spec_to_data_url($spec_key);
471 10 ▶ if ($base) {
Branch: 1 branch
BranchTrueFalseTotal
if ($base) 10 0 10
472 10 ▶ my $sep = ($base =~ /\?/) ? '&' : '?';
Branch: 1 branch
BranchTrueFalseTotal
$base =~ /\?/ ? : 0 10 10
473 10 $drill_url = $base . $sep
474 . 'f=' . $enc_date . ':starts:' . urlencode($cur)
475 . '&f=' . $enc_cat . ':eq:' . $gval_enc;
476 10 ▶ if ($base_url) {
Branch: 1 branch
BranchTrueFalseTotal
if ($base_url) 10 0 10
477 10 $drill_url .= '&back2=' . urlencode($base_url)
478 . '&back2_label=Back+to+insights';
479 }
480 }
481  
482 10 ▶ push @insights, {
Branch: 1 branch
BranchTrueFalseTotal
$drill_url ? : 10 0 10
483 kind => 'top_mover',
484 headline => $headline,
485 detail => $detail,
486 view_url => $view_url,
487 ($drill_url ? (drill_url => $drill_url) : ()),
488 method => 'top_mover',
489 };
490 }
491  
492 4 return @insights;
493 }
494  
495 # _seasonality($records, $date_col, $num_col, $spec_key, $base_url) -> @insights
496 #
497 # Computes period sums (year-month), then calculates the lag-1 autocorrelation
498 # coefficient on the sequence of sums. A coefficient >= SEASON_R_THRESHOLD
499 # is flagged as seasonal.
500 #
501 # Autocorrelation formula:
502 # r(1) = sum_{t=1}^{n-1}[(x_t - xbar)(x_{t+1} - xbar)] / sum_{t=0}^{n-1}[(x_t - xbar)^2]
503 #
504 # This is the Pearson correlation between the series and itself shifted by one
505 # period. For a regular, evenly-spaced time series this is the lag-1 Moran
506 # coefficient. With uneven spacing it is an approximation, but adequate for
507 # detecting year-over-year periodicity in monthly business data.
508 89 ▶sub _seasonality {
Subroutine _seasonality: called CC=10 SCAR=23.1
509 89 my ($records, $date_col, $num_col, $spec_key, $base_url) = @_;
510  
511 89 my %period_totals;
512 89 for my $row (@$records) {
513 561 ▶ my $date = $row->{$date_col} // '';
Condition50%1/2
$$row{$date_col} // ""
l!l
5610
MC/DC0%0/1
$$row{$date_col} // ""
$$row{$date_col}
Truth table50%1/2
A
A= $$row{$date_col}
Aresult
11
00
514 561 ▶ next unless $date =~ /\A(\d{4}-\d{2})/;
Branch: 1 branch
BranchTrueFalseTotal
unless $date =~ /\A(\d{4}-\d{2})/ 0 561 561
515 561 my $period = $1;
516 561 my $val = _to_num($row->{$num_col});
517 561 ▶ next unless defined $val;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $val 0 561 561
518 561 $period_totals{$period} += $val;
519 }
520  
521 89 my @periods = sort keys %period_totals;
522 89 ▶ return () unless @periods >= $MIN_PERIODS_SEASON;
Branch: 1 branch
BranchTrueFalseTotal
unless @periods >= $MIN_PERIODS_SEASON 84 5 89
523  
524 5 my @y = map { $period_totals{$_} } @periods;
525 5 my $n = scalar @y;
526 5 my $ybar = (sum(@y)) / $n;
527  
528 5 my $num = 0;
529 5 my $denom = 0;
530 5 for my $i (0 .. $n - 1) {
531 60 my $dev = $y[$i] - $ybar;
532 60 $denom += $dev * $dev;
533 60 ▶ $num += $dev * ($y[$i + 1] - $ybar) if $i < $n - 1;
Branch: 1 branch
BranchTrueFalseTotal
if $i < $n - 1 55 5 60
534 }
535  
536 5 ▶ return () unless $denom > 0;
Branch: 1 branch
BranchTrueFalseTotal
unless $denom > 0 1 4 5
537 4 my $r = $num / $denom;
538  
539 4 ▶ return () unless $r >= $SEASON_R_THRESHOLD;
Branch: 1 branch
BranchTrueFalseTotal
unless $r >= $SEASON_R_THRESHOLD 3 1 4
540  
541 1 my $headline = sprintf('%s shows a repeating period-over-period pattern (r=%.2f)',
542 $num_col, $r);
543 1 my $detail = sprintf(
544 'Lag-1 autocorrelation %.2f across %d periods (%s to %s)',
545 $r, $n, $periods[0], $periods[-1]
546 );
547  
548 1 my $enc_spec = urlencode($spec_key);
549 1 my $enc_date = urlencode($date_col);
550 1 my $enc_num = urlencode($num_col);
551 1 my $view_url = "/graph?l=$enc_spec&x=$enc_date&y=$enc_num";
552  
553 return ({
554 1 kind => 'seasonality',
555 headline => $headline,
556 detail => $detail,
557 view_url => $view_url,
558 method => 'autocorr',
559 });
560 }
561  
562 # _fmt_num($n) -> $string
563 # Format a number to 4 significant figures, stripping trailing zeros.
564 94 ▶sub _fmt_num {
Subroutine _fmt_num: called CC=4 SCAR=13.9
565 94 my $n = shift;
566 94 ▶ return 'N/A' unless defined $n;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $n 1 93 94
567 93 ▶ return sprintf('%s', $n) if $n == int($n) && abs($n) < 1e12;
Branch: 1 branch
BranchTrueFalseTotal
if $n == int $n and abs $n < '1000000000000' 90 3 93
Condition66%2/3
$n == int $n and abs $n < '1000000000000'
!ll&&!rl&&r
3090
MC/DC50%1/2
$n == int $n and abs $n < '1000000000000'
$n == int $n abs $n < '1000000000000'
Truth table66%2/3
A and B
A= $n == int $nB= abs $n < '1000000000000'
ABresult
0X0
100
111
568 3 return sprintf('%s', 0 + sprintf('%.4g', $n));
569 }
570  
571  
572 # _spec_to_data_url($spec_key) -> $url_or_undef
573 #
574 # Converts a l= spec key to the root-relative URL of the data table page.
575 # table:sales -> /view/sales
576 # path:/abs/path/f.csv -> /open?path=%2Fabs%2Fpath%2Ff.csv
577 # Returns undef for unrecognised spec forms.
578 32 ▶sub _spec_to_data_url {
Subroutine _spec_to_data_url: called CC=5 SCAR=16.1
579 32 ▶ my $spec = shift // '';
Condition100%2/2
shift() // ""
l!l
311
MC/DC100%1/1
shift() // ""
shift()
Truth table100%2/2
A
A= shift()
Aresult
11
00
580 32 ▶ if ($spec =~ /\Atable:(.+)\z/) {
Branch: 1 branch
BranchTrueFalseTotal
if ($spec =~ /\Atable:(.+)\z/) 22 10 32
581 22 return '/view/' . urlencode($1);
582 }
583 10 ▶ if ($spec =~ /\Apath:(.+)\z/) {
Branch: 1 branch
BranchTrueFalseTotal
if ($spec =~ /\Apath:(.+)\z/) 2 8 10
584 2 require Database::BI::Model::DataSource;
585 2 my ($file, $internal) = Database::BI::Model::DataSource::split_path($1);
586 2 ▶ return '/open?path=' . urlencode($file) . (defined $internal ? '&it=' . urlencode($internal) : '');
Branch: 1 branch
BranchTrueFalseTotal
defined $internal ? : 0 2 2
587 }
588 8 return undef;
589 }
590  
591 1 1;
592  
593 __END__
« blib/lib/Database/BI/Command/password.pm ↑ Summary blib/lib/Database/BI/Model/Geocode.pm »