| 1 | package API::Eulerian::EDW::UseCases::SanKeyMap; | ||
| 2 | |||
| 3 | use API::Eulerian::EDW::Sql::Builder; | ||
| 4 | use strict; use warnings; | ||
| 5 | use base 'Exporter'; | ||
| 6 | |||
| 7 | our @EXPORT = qw( Sql ); | ||
| 8 | |||
| 9 | sub AddTimerange | ||
| 10 | { | ||
| 11 | my ( $builder, $setup ) = @_; | ||
| 12 | $builder->timerange( | ||
| 13 | $setup->{ from }, $setup->{ to } | ||
| 14 | ); | ||
| 15 | } | ||
| 16 | |||
| 17 | sub ToArray | ||
| 18 | { | ||
| 19 | my ( $hash, $key ) = @_; | ||
| 20 | my $value = $hash->{ $key } || []; | ||
| 21 | $value = [ $value ] if ref( $value ) ne 'ARRAY'; | ||
| 22 | return $value; | ||
| 23 | } | ||
| 24 | |||
| 25 | sub AddPageview | ||
| 26 | { | ||
| 27 | my ( $builder, $setup ) = @_; | ||
| 28 | my $devices = ToArray( $setup, 'mdevicetypefull-name' ); | ||
| 29 | my $subkeys = ToArray( $setup, 'subkey2-name' ); | ||
| 30 | my $ndevices = scalar( @$devices ); | ||
| 31 | my $nsubkeys = scalar( @$subkeys ); | ||
| 32 | my $filter = ''; | ||
| 33 | |||
| 34 | # Handle pageview filtering on devices if any | ||
| 35 | if( $ndevices == 1 ) { | ||
| 36 | $filter .= 'pageview.device.devicetype.type == '; | ||
| 37 | $filter .= "'"; | ||
| 38 | $filter .= $devices->[ 0 ]; | ||
| 39 | $filter .= "'"; | ||
| 40 | } elsif( $ndevices > 1 ) { | ||
| 41 | $filter .= 'IN( pageview.device.devicetype.type, '; | ||
| 42 | $filter .= join( ', ', map { '"' . $_ . '"' } @$devices ); | ||
| 43 | $filter .= ' )'; | ||
| 44 | } | ||
| 45 | |||
| 46 | # Handle pageview filtering on subkey2 if any | ||
| 47 | if( $nsubkeys == 1 ) { | ||
| 48 | if( $ndevices ) { $filter .= " && "; } | ||
| 49 | $filter .= 'pageview.subkey2.name == '; | ||
| 50 | $filter .= "'"; | ||
| 51 | $filter .= $subkeys->[ 0 ]; | ||
| 52 | $filter .= "'"; | ||
| 53 | } elsif( $nsubkeys > 1 ) { | ||
| 54 | if( $ndevices ) { $filter .= " && "; } | ||
| 55 | $filter .= 'IN( pageview.subkey2.name, '; | ||
| 56 | $filter .= join( ', ', map { '"' . $_ . '"' } @$subkeys ); | ||
| 57 | $filter .= ' )'; | ||
| 58 | } | ||
| 59 | |||
| 60 | # Add pageview reader | ||
| 61 | $builder->readers( | ||
| 62 | 'pageview', 'ea:pageview', $setup->{ site }, $filter | ||
| 63 | ); | ||
| 64 | |||
| 65 | } | ||
| 66 | |||
| 67 | sub AddClickview | ||
| 68 | { | ||
| 69 | my ( $builder, $setup ) = @_; | ||
| 70 | my $medias = ToArray( $setup, 'media-shortname' ); | ||
| 71 | my $nmedias = scalar( @$medias ); | ||
| 72 | my $filter = ''; | ||
| 73 | |||
| 74 | # Handle clickview if need it */ | ||
| 75 | if( $nmedias == 1 ) { | ||
| 76 | # Setup clickview filter | ||
| 77 | $filter .= "clickview.channel.odmedia == '"; | ||
| 78 | $filter .= $medias->[ 0 ]; | ||
| 79 | $filter .= "'"; | ||
| 80 | } elsif( $nmedias > 1 ) { | ||
| 81 | $filter .= 'IN( clickview.channel.odmedia, '; | ||
| 82 | $filter .= join( ', ', map { '"' . $_ . '"' } @$medias ); | ||
| 83 | $filter .= ' )'; | ||
| 84 | } | ||
| 85 | # Add clickview reader | ||
| 86 | if( $filter ne '' ) { | ||
| 87 | $builder->readers( | ||
| 88 | 'clickview', 'ea:clickview', $setup->{ site }, $filter | ||
| 89 | ); | ||
| 90 | } | ||
| 91 | |||
| 92 | } | ||
| 93 | |||
| 94 | sub AddVisit | ||
| 95 | { | ||
| 96 | my ( $builder, $setup ) = @_; | ||
| 97 | my $filter; | ||
| 98 | |||
| 99 | # Setup visit filter | ||
| 100 | $filter = 'visit.last.pageview.timestamp + MINS( '; | ||
| 101 | $filter .= int( $setup->{ session } || 30 ); | ||
| 102 | $filter .= ' ) < pageview.timestamp'; | ||
| 103 | |||
| 104 | # Create visit | ||
| 105 | $builder->groups( 'visit', 'pageview', $filter ); | ||
| 106 | |||
| 107 | } | ||
| 108 | |||
| 109 | sub AddJoin | ||
| 110 | { | ||
| 111 | my ( $builder, $setup ) = @_; | ||
| 112 | my $media = $setup->{ 'media-shortname' } || undef; | ||
| 113 | my $imedia = int( $setup->{ 'media-id' } || 0 ); | ||
| 114 | |||
| 115 | if( $imedia ) { | ||
| 116 | # Setup join filter | ||
| 117 | my $filter = ''; | ||
| 118 | $filter .= "clickview.timestamp > visit.first.pageview.timestamp && "; | ||
| 119 | $filter .= "clickview.timestamp <= visit.last.pageview.timestamp && "; | ||
| 120 | $filter .= "join.count() < 1 "; | ||
| 121 | # Add join on clickview | ||
| 122 | $builder->joins( 'join', 'visit', 'clickview', $filter ); | ||
| 123 | } | ||
| 124 | |||
| 125 | return $imedia; | ||
| 126 | } | ||
| 127 | |||
| 128 | sub AddOutputs | ||
| 129 | { | ||
| 130 | my ( $builder, $prefix, $setup ) = @_; | ||
| 131 | my $npages = int( $setup->{ npages } || 5 ); | ||
| 132 | |||
| 133 | # Add outputs | ||
| 134 | for my $ioutput ( 0 ... $npages - 1 ) { | ||
| 135 | my $output = $prefix; | ||
| 136 | $output .= "visit.items( $ioutput ).pageview.page.name"; | ||
| 137 | $builder->outputs( $output ); | ||
| 138 | } | ||
| 139 | |||
| 140 | } | ||
| 141 | |||
| 142 | sub AddFilter | ||
| 143 | { | ||
| 144 | my ( $builder, $prefix, $setup ) = @_; | ||
| 145 | my $roots = ToArray( $setup, 'roots' ); | ||
| 146 | |||
| 147 | # Add outputs filter | ||
| 148 | if( scalar( @$roots ) > 0 ) { | ||
| 149 | my $filter = ''; | ||
| 150 | |||
| 151 | if( $prefix ne '' ) { | ||
| 152 | $filter .= $prefix; | ||
| 153 | $filter .= "visit.first.pageview.timestamp != NULL && "; | ||
| 154 | } | ||
| 155 | if( scalar( @$roots ) == 1 ) { | ||
| 156 | $filter .= $prefix; | ||
| 157 | $filter .= "visit.items( 0 ).pageview.page.name == '"; | ||
| 158 | $filter .= $roots->[ 0 ]; | ||
| 159 | $filter .= "'"; | ||
| 160 | } elsif( scalar( @$roots ) > 1 ) { | ||
| 161 | $filter .= "IN( "; | ||
| 162 | $filter .= $prefix; | ||
| 163 | $filter .= "visit.items( 0 ).pageview.page.name, "; | ||
| 164 | $filter .= join( ', ', map { '"' . $_ . '"' } @$roots ); | ||
| 165 | $filter .= " )"; | ||
| 166 | } | ||
| 167 | $builder->filter( $filter ); | ||
| 168 | |||
| 169 | } | ||
| 170 | |||
| 171 | } | ||
| 172 | |||
| 173 | sub AddThen | ||
| 174 | { | ||
| 175 | my ( $builder, $setup ) = @_; | ||
| 176 | my $palmares = int( $setup->{ npalmares } || 5 ); | ||
| 177 | $builder->thens( "SanKeyMap( $palmares )" ); | ||
| 178 | } | ||
| 179 | |||
| 180 | sub Sql | ||
| 181 | { | ||
| 182 | my $builder = new API::Eulerian::EDW::Sql::Builder(); | ||
| 183 | my $master = 'visit'; | ||
| 184 | my ( $setup ) = @_; | ||
| 185 | my $prefix = ''; | ||
| 186 | |||
| 187 | $builder->mode( 'ROW' ); | ||
| 188 | $builder->master( 'visit' ); | ||
| 189 | AddTimerange( $builder, $setup ); | ||
| 190 | AddPageview( $builder, $setup ); | ||
| 191 | AddClickview( $builder, $setup ); | ||
| 192 | AddVisit( $builder, $setup ); | ||
| 193 | if( AddJoin( $builder, $setup ) ) { | ||
| 194 | $builder->master( 'join' ); | ||
| 195 | $prefix = 'join.'; | ||
| 196 | } | ||
| 197 | AddOutputs( $builder, $prefix, $setup ); | ||
| 198 | AddFilter( $builder, $prefix, $setup ); | ||
| 199 | AddThen( $builder, $setup ); | ||
| 200 | |||
| 201 | return $builder->str(); | ||
| 202 | } |