Longitude and Latitude in SQL

Nancy Chelaru-Centea

Feb. 18, 2019


Longitude and Latitude in SQL

WITH HS as (select orderid, productid from orderline group by orderid, productid),
     CNT as (select productid,
                    sum(cnt) as cnt
             from (select orderid,
                          productid,
                          1 as cnt
                   from orderline
                   group by orderid, productid) ol
             group by productid),
     NUM_ORDERS as (select count(*) as numorders from orders)


SELECT lhs.productid                                as l_productid,
       rhs.productid                                as r_productid,
       count(*)                                     as numlhsrhs,
       lcnt.cnt                                     as l_cnt,
       rcnt.cnt                                     as r_cnt,
       numorders,
       count(*) / numorders                         as support,
       count(*) / lcnt.cnt                          as confidence,
       count(*) * numorders / (lcnt.cnt * rcnt.cnt) as lift


from HS lhs
       JOIN HS rhs
            ON lhs.orderid = rhs.orderid
              AND lhs.productid <> rhs.productid
       JOIN CNT LCNT
            ON LHS.productid = lcnt.productid
       JOIN CNT RCNT
            ON RHS.productid = rcnt.productid
       JOIN NUM_ORDERS


GROUP BY l_productid,                   /* Grouping by each unique product ID pair should be enough to identify each rule */  
         r_productid,
         l_cnt,                         /* So is it necessary to also group by l_cnt, r_cnt and numorders? */
         r_cnt,                      
         numorders

ORDER BY support desc,
         lhs.productid,
         rhs.productid

LIMIT 10
l_productid r_productid numlhsrhs l_cnt r_cnt numorders support confidence lift
12820 13190 2588 18441 3404 192983 0.0134 0.1403 7.9563
13190 12820 2588 3404 18441 192983 0.0134 0.7603 7.9563
12819 12820 1848 1960 18441 192983 0.0096 0.9429 9.8669
12820 12819 1848 18441 1960 192983 0.0096 0.1002 9.8669
11048 11196 1824 3166 4729 192983 0.0095 0.5761 23.5106
11196 11048 1824 4729 3166 192983 0.0095 0.3857 23.5106
10956 12139 1483 2773 7063 192983 0.0077 0.5348 14.6124
12139 10956 1483 7063 2773 192983 0.0077 0.2100 14.6124
12139 12820 1246 7063 18441 192983 0.0065 0.1764 1.8461
12820 12139 1246 18441 7063 192983 0.0065 0.0676 1.8461