-
Type:
Bug
-
Status: Open
-
Priority:
Blocker
-
Resolution: Unresolved
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: Operators
-
Labels:None
Hello,
I am trying to divide the values of two fields that have been joined together on multiple criteria (offerlevelid, visit_date, days_to_action). For some rows, the quotient is correct, but for other rows, the result is zero. See below:
TABLE A: mlee.mit_test1
select * from mlee.mit_test1 limit 5;
| offerlevelid | action_date | visit_date | days_to_action | cluster | cnt |
|---|---|---|---|---|---|
| 29992 | 2018-07-11 | 2018-06-28 | 13 | 11158 | 1 |
| 29991 | 2018-07-12 | 2018-06-18 | 24 | 11158 | 0 |
| 5279 | 2018-07-01 | 2018-05-30 | 32 | 11158 | 10 |
| 5279 | 2018-07-01 | 2018-06-02 | 29 | 11158 | 1 |
| 5279 | 2018-07-02 | 2018-06-29 | 3 | 11158 | 3 |
TABLE B: mlee_p2p.num_at_visit_vd
select * from mlee_p2p.num_at_visit_vd limit 5;
| offerlevelid | action_date | visit_date | days_to_action | cnt |
|---|---|---|---|---|
| 5279 | 2018-07-06 | 2018-06-17 | 19 | 1696 |
| 5279 | 2018-07-07 | 2018-06-07 | 30 | 2072 |
| 29991 | 2018-07-11 | 2018-07-09 | 2 | 361 |
| 29991 | 2018-07-10 | 2018-06-10 | 30 | 116 |
| 29992 | 2018-07-02 | 2018-06-27 | 5 | 0 |
When I attempt to perform division on a.cnt / b.cnt, the results do not make sense. Specifically, there are results of zero where a.cnt and b.cnt are integer values. I tried casting both as doubles, but that did not work either. See below, where I've bolded the "prob" values that do not make sense. Please advise!
select
a.offerlevelid,
a.days_to_action,
a.visit_date,
a.cluster,
a.cnt at_cluster_vd_dta_cnt,
b.cnt at_vd_dta_cnt,
a.cnt/b.cnt prob
from mlee.mit_test1 a
join mlee_p2p.num_at_visit_vd b on a.offerlevelid=b.offerlevelid
and a.visit_date = b.visit_date
and a.days_to_action = b.days_to_action
order by a.days_to_action,a.visit_date
limit 2000;
| offerlevelid | days_to_action | visit_date | cluster | at_cluster_vd_dta_cnt | at_vd_dta_cnt | prob |
|---|---|---|---|---|---|---|
| 29991 | 0 | 2018-07-01 | 11158 | 1 | 111 | .009009009009009009 |
| 5279 | 0 | 2018-07-01 | 11158 | 8 | 3255 | 0.002457757296466974 |
| 29992 | 0 | 2018-07-02 | 11158 | 0 | 1 | 0.0 |
| 29991 | 0 | 2018-07-02 | 11158 | 2 | 247 | 0.0 |
| 5279 | 0 | 2018-07-02 | 11158 | 3 | 2268 | 0.0013227513227513227 |
| 5279 | 0 | 2018-07-03 | 11158 | 4 | 3206 | 0.0012476606363069245 |
| 29991 | 0 | 2018-07-03 | 11158 | 1 | 293 | 0.0 |
| 5279 | 0 | 2018-07-04 | 11158 | 4 | 3523 | 0.0011353959693443088 |
| 29991 | 0 | 2018-07-04 | 11158 | 2 | 203 | 0.009852216748768473 |
| 29992 | 0 | 2018-07-05 | 11158 | 0 | 2 | 0.0 |