| Line 66... |
Line 66... |
| 66 |
<modified_date>2012/08/26 11:16:19.521</modified_date>
|
66 |
<modified_date>2012/08/26 11:16:19.521</modified_date>
|
| 67 |
</info>
|
67 |
</info>
|
| 68 |
<notepads>
|
68 |
<notepads>
|
| 69 |
</notepads>
|
69 |
</notepads>
|
| 70 |
<connection>
|
70 |
<connection>
|
| 71 |
<name>salesLocal</name>
|
71 |
<name>salesLocalTest</name>
|
| 72 |
<server>localhost</server>
|
72 |
<server>localhost</server>
|
| 73 |
<type>MYSQL</type>
|
73 |
<type>MYSQL</type>
|
| 74 |
<access>Native</access>
|
74 |
<access>Native</access>
|
| 75 |
<database>sales</database>
|
75 |
<database>sales</database>
|
| 76 |
<port>3306</port>
|
76 |
<port>3306</port>
|
| Line 152... |
Line 152... |
| 152 |
<copies>1</copies>
|
152 |
<copies>1</copies>
|
| 153 |
<partitioning>
|
153 |
<partitioning>
|
| 154 |
<method>none</method>
|
154 |
<method>none</method>
|
| 155 |
<schema_name/>
|
155 |
<schema_name/>
|
| 156 |
</partitioning>
|
156 |
</partitioning>
|
| 157 |
<connection>salesLocal</connection>
|
157 |
<connection>salesLocalTest</connection>
|
| 158 |
<schema>sales</schema>
|
158 |
<schema>sales</schema>
|
| 159 |
<table>repeatSales</table>
|
159 |
<table>repeatSales</table>
|
| 160 |
<commit>1000</commit>
|
160 |
<commit>1000</commit>
|
| 161 |
<truncate>Y</truncate>
|
161 |
<truncate>Y</truncate>
|
| 162 |
<ignore_errors>N</ignore_errors>
|
162 |
<ignore_errors>N</ignore_errors>
|
| Line 235... |
Line 235... |
| 235 |
<field>
|
235 |
<field>
|
| 236 |
<column_name>item2</column_name>
|
236 |
<column_name>item2</column_name>
|
| 237 |
<stream_name>item2</stream_name>
|
237 |
<stream_name>item2</stream_name>
|
| 238 |
</field>
|
238 |
</field>
|
| 239 |
<field>
|
239 |
<field>
|
| - |
|
240 |
<column_name>averageValue</column_name>
|
| - |
|
241 |
<stream_name>averageValue</stream_name>
|
| - |
|
242 |
</field>
|
| - |
|
243 |
<field>
|
| 240 |
<column_name>daysToPurchase</column_name>
|
244 |
<column_name>daysToFirstPurchase</column_name>
|
| 241 |
<stream_name>daysToPurchase</stream_name>
|
245 |
<stream_name>daysToFirstPurchase</stream_name>
|
| - |
|
246 |
</field>
|
| - |
|
247 |
<field>
|
| - |
|
248 |
<column_name>daysToSecondPurchase</column_name>
|
| - |
|
249 |
<stream_name>daysToSecondPurchase</stream_name>
|
| 242 |
</field>
|
250 |
</field>
|
| 243 |
</fields>
|
251 |
</fields>
|
| 244 |
<cluster_schema/>
|
252 |
<cluster_schema/>
|
| 245 |
<remotesteps> <input> </input> <output> </output> </remotesteps> <GUI>
|
253 |
<remotesteps> <input> </input> <output> </output> </remotesteps> <GUI>
|
| 246 |
<xloc>589</xloc>
|
254 |
<xloc>589</xloc>
|
| Line 261... |
Line 269... |
| 261 |
</partitioning>
|
269 |
</partitioning>
|
| 262 |
<connection>tranProd</connection>
|
270 |
<connection>tranProd</connection>
|
| 263 |
<sql>select
|
271 |
<sql>select
|
| 264 |
o1.customer_id, temp.orderCount,
|
272 |
o1.customer_id, temp.orderCount,
|
| 265 |
case when temp.grandTotal<250 then 0 when temp.grandTotal<500 then 1 when temp.grandTotal<1000 then 2 when temp.grandTotal<2000 then 3 when temp.grandTotal < 5000 then 4 when temp.grandTotal < 10000 then 5 when temp.grandTotal < 15000 then 6 when temp.grandTotal <20000 then 7 when temp.grandTotal < 50000 then 8 else 9 end as grandTotal,
|
273 |
case when temp.grandTotal<250 then 0 when temp.grandTotal<500 then 1 when temp.grandTotal<1000 then 2 when temp.grandTotal<2000 then 3 when temp.grandTotal < 5000 then 4 when temp.grandTotal < 10000 then 5 when temp.grandTotal < 15000 then 6 when temp.grandTotal <20000 then 7 when temp.grandTotal < 50000 then 8 else 9 end as grandTotal,
|
| - |
|
274 |
case when (temp.averageValue)<250 then 0 when temp.averageValue<500 then 1 when temp.averageValue<1000 then 2 when temp.averageValue<2000 then 3 when temp.averageValue < 5000 then 4 when temp.averageValue < 10000 then 5 when temp.averageValue < 15000 then 6 when temp.averageValue <20000 then 7 when temp.averageValue < 50000 then 8 else 9 end as averageValue,
|
| 266 |
datediff(o1.created_timestamp, '20101231') as date_id1, datediff(o2.created_timestamp, '20101231') as date_id2,
|
275 |
datediff(o1.created_timestamp, '20101231') as date_id1, datediff(o2.created_timestamp, '20101231') as date_id2,
|
| 267 |
case when o1.cod = 1 then 1 when o1.cod = 0 then 0 else null end as paymode1,
|
276 |
case when o1.cod = 1 then 1 when o1.cod = 0 then 0 else null end as paymode1,
|
| 268 |
case when o2.cod = 1 then 1 when o2.cod = 0 then 0 else null end as paymode2,
|
277 |
case when o2.cod = 1 then 1 when o2.cod = 0 then 0 else null end as paymode2,
|
| 269 |
case when o1.total_amount<250 then 0 when o1.total_amount<500 then 1 when o1.total_amount<1000 then 2 when o1.total_amount<2000 then 3 when o1.total_amount < 5000 then 4 when o1.total_amount < 10000 then 5 when o1.total_amount < 15000 then 6 when o1.total_amount <20000 then 7 when o1.total_amount < 50000 then 8 else 9 end as price_range1,
|
278 |
case when o1.total_amount<250 then 0 when o1.total_amount<500 then 1 when o1.total_amount<1000 then 2 when o1.total_amount<2000 then 3 when o1.total_amount < 5000 then 4 when o1.total_amount < 10000 then 5 when o1.total_amount < 15000 then 6 when o1.total_amount <20000 then 7 when o1.total_amount < 50000 then 8 else 9 end as price_range1,
|
| 270 |
case when o2.total_amount is null then 99 when o2.total_amount<250 then 0 when o2.total_amount<500 then 1 when o2.total_amount<1000 then 2 when o2.total_amount<2000 then 3 when o2.total_amount < 5000 then 4 when o2.total_amount < 10000 then 5 when o2.total_amount < 15000 then 6 when o2.total_amount <20000 then 7 when o2.total_amount < 50000 then 8 else 9 end as price_range2,
|
279 |
case when o2.total_amount is null then 99 when o2.total_amount<250 then 0 when o2.total_amount<500 then 1 when o2.total_amount<1000 then 2 when o2.total_amount<2000 then 3 when o2.total_amount < 5000 then 4 when o2.total_amount < 10000 then 5 when o2.total_amount < 15000 then 6 when o2.total_amount <20000 then 7 when o2.total_amount < 50000 then 8 else 9 end as price_range2,
|
| Line 274... |
Line 283... |
| 274 |
case when t2.session_source is null then 'Unknown' when t2.session_source like 'DIRECT%' then 'Direct' when t2.session_source like 'ORGANIC%' then 'Organic' when t2.session_source like 'PAID%' then 'Paid' else 'Referer' end as session_source_type2,
|
283 |
case when t2.session_source is null then 'Unknown' when t2.session_source like 'DIRECT%' then 'Direct' when t2.session_source like 'ORGANIC%' then 'Organic' when t2.session_source like 'PAID%' then 'Paid' else 'Referer' end as session_source_type2,
|
| 275 |
SUBSTRING_INDEX(REPLACE(REPLACE(SUBSTR(t2.session_source, LOCATE('http://', LCASE(t2.session_source)), LENGTH(t2.session_source)), 'http://', ''), 'www.', ''), '/', 1) as session_source_url2,
|
284 |
SUBSTRING_INDEX(REPLACE(REPLACE(SUBSTR(t2.session_source, LOCATE('http://', LCASE(t2.session_source)), LENGTH(t2.session_source)), 'http://', ''), 'www.', ''), '/', 1) as session_source_url2,
|
| 276 |
l1.item_id as item1, l2.item_id as item2,
|
285 |
l1.item_id as item1, l2.item_id as item2,
|
| 277 |
case when datediff(o1.created_timestamp, u.active_since) > 30 then 0 when datediff(o1.created_timestamp, u.active_since) > 15 then 1
|
286 |
case when datediff(o1.created_timestamp, u.active_since) > 30 then 0 when datediff(o1.created_timestamp, u.active_since) > 15 then 1
|
| 278 |
when datediff(o1.created_timestamp, u.active_since) > 7 then 2 when datediff(o1.created_timestamp, u.active_since) > 2 then 3
|
287 |
when datediff(o1.created_timestamp, u.active_since) > 7 then 2 when datediff(o1.created_timestamp, u.active_since) > 2 then 3
|
| 279 |
when datediff(o1.created_timestamp, u.active_since) > 1 then 4 else 5 end as daysToPurchase
|
288 |
when datediff(o1.created_timestamp, u.active_since) > 1 then 4 else 5 end as daysToFirstPurchase,
|
| - |
|
289 |
case when o2.created_timestamp is null then 99 when datediff(o2.created_timestamp, o1.created_timestamp) > 30 then 0 when datediff(o2.created_timestamp, o1.created_timestamp) > 15 then 1
|
| - |
|
290 |
when datediff(o2.created_timestamp, o1.created_timestamp) > 7 then 2 when datediff(o2.created_timestamp, o1.created_timestamp) > 2 then 3
|
| - |
|
291 |
when datediff(o2.created_timestamp, o1.created_timestamp) > 1 then 4 else 5 end as daysToSecondPurchase
|
| - |
|
292 |
|
| 280 |
from
|
293 |
from
|
| 281 |
(select (count(o.id)+1) as orderCount, m.min_orderId, min(o.id) as second_min_orderId, m.grandTotal
|
294 |
(select (count(o.id)+1) as orderCount, m.min_orderId, min(o.id) as second_min_orderId, m.grandTotal, (m.grandTotal/(count(o.id)+1)) as averageValue
|
| 282 |
from `order` o
|
295 |
from `order` o
|
| 283 |
right join
|
296 |
right join
|
| 284 |
(select customer_id, sum(total_amount) as grandTotal, min(id) as min_orderId
|
297 |
(select customer_id, sum(total_amount) as grandTotal, min(id) as min_orderId
|
| 285 |
from `order` o
|
298 |
from `order` o
|
| 286 |
where status not in (0,1,8,11,28,29,30,47,49,53,55,69,71,73,75,78)
|
299 |
where status not in (0,1,8,11,28,29,30,47,49,53,55,69,71,73,75,78)
|