User:Statsrick/SQL code

Do a "fuzzy merge" using SQL

create table tmp1 as select canonical_url url1 from table1 group by 1; create table tmp2 as select substring(url from 5 for 10) url_part,url as url2 from table2 group by 1,2; /* note no where statement...cartesian join. use having to pick out closest match */ create table tmp3 as select *,a.url1 like '%'||b.url_part||'%' as comp from tmp1 a,tmp2 b group by a.url1,b.url_part,b.url2 having a.url1 like '%'||b.url_part||'%';

Replicate URLDECODE in a single statement using PostgreSQL

select referrer ,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(referrer,'%3A',':'),'%2F','/'),'%28','('),'%29',')'),'%3B',';'),'%21','!'),'%40','@'),'%26','&'),'%3D','='),'%2B','+'),'%24','$'),'%2C',','),'%2A','*'),'%3F','?'),'%23','#'),'%5B','['),'%5D',']'),'%25','%'),'%3A',':'),'%2F','/'),'%20',' ') as t from temp;

Determine DMA based on closest LAT LON coordinates

,TRUNC(LEAST( TRUNC(SQRT((69.1*(40.714353-LAT))**2+(53*(-74.005973-LON))**2)*1e6)+0.501, TRUNC(SQRT((69.1*(34.052234-LAT))**2+(53*(-118.243685-LON))**2)*1e6)+0.803, TRUNC(SQRT((69.1*(41.878114-LAT))**2+(53*(-87.629798-LON))**2)*1e6)+0.602, TRUNC(SQRT((69.1*(39.952335-LAT))**2+(53*(-75.163789-LON))**2)*1e6)+0.504, TRUNC(SQRT((69.1*(32.780140-LAT))**2+(53*(-96.800451-LON))**2)*1e6)+0.623, TRUNC(SQRT((69.1*(37.773143-LAT))**2+(53*(-122.437367-LON))**2)*1e6)+0.807, TRUNC(SQRT((69.1*(42.927909-LAT))**2+(53*(-71.438502-LON))**2)*1e6)+0.506, TRUNC(SQRT((69.1*(39.468972-LAT))**2+(53*(-77.564350-LON))**2)*1e6)+0.511, TRUNC(SQRT((69.1*(33.748995-LAT))**2+(53*(-84.387982-LON))**2)*1e6)+0.524, TRUNC(SQRT((69.1*(29.760193-LAT))**2+(53*(-95.369390-LON))**2)*1e6)+0.618, TRUNC(SQRT((69.1*(42.331427-LAT))**2+(53*(-83.045754-LON))**2)*1e6)+0.505, TRUNC(SQRT((69.1*(47.444430-LAT))**2+(53*(-122.300497-LON))**2)*1e6)+0.819, TRUNC(SQRT((69.1*(34.503326-LAT))**2+(53*(-112.235843-LON))**2)*1e6)+0.753, TRUNC(SQRT((69.1*(27.336435-LAT))**2+(53*(-82.530653-LON))**2)*1e6)+0.539, TRUNC(SQRT((69.1*(45.247260-LAT))**2+(53*(-93.455390-LON))**2)*1e6)+0.613, TRUNC(SQRT((69.1*(25.788969-LAT))**2+(53*(-80.226439-LON))**2)*1e6)+0.528, TRUNC(SQRT((69.1*(39.737567-LAT))**2+(53*(-104.984718-LON))**2)*1e6)+0.751, TRUNC(SQRT((69.1*(41.092388-LAT))**2+(53*(-82.221608-LON))**2)*1e6)+0.51, TRUNC(SQRT((69.1*(28.538336-LAT))**2+(53*(-81.379237-LON))**2)*1e6)+0.534, TRUNC(SQRT((69.1*(37.917594-LAT))**2+(53*(-121.171039-LON))**2)*1e6)+0.862, TRUNC(SQRT((69.1*(38.627003-LAT))**2+(53*(-90.199404-LON))**2)*1e6)+0.609, TRUNC(SQRT((69.1*(45.523452-LAT))**2+(53*(-122.676207-LON))**2)*1e6)+0.82, TRUNC(SQRT((69.1*(40.440625-LAT))**2+(53*(-79.995886-LON))**2)*1e6)+0.508, TRUNC(SQRT((69.1*(35.772096-LAT))**2+(53*(-78.638615-LON))**2)*1e6)+0.56, TRUNC(SQRT((69.1*(35.227087-LAT))**2+(53*(-80.843127-LON))**2)*1e6)+0.517, TRUNC(SQRT((69.1*(39.768583-LAT))**2+(53*(-86.157956-LON))**2)*1e6)+0.527, TRUNC(SQRT((69.1*(39.290385-LAT))**2+(53*(-76.612189-LON))**2)*1e6)+0.512, TRUNC(SQRT((69.1*(32.715329-LAT))**2+(53*(-117.157255-LON))**2)*1e6)+0.825, TRUNC(SQRT((69.1*(36.166667-LAT))**2+(53*(-86.783333-LON))**2)*1e6)+0.659, TRUNC(SQRT((69.1*(32.726416-LAT))**2+(53*(-96.993022-LON))**2)*1e6)+0.533, TRUNC(SQRT((69.1*(39.099727-LAT))**2+(53*(-94.578567-LON))**2)*1e6)+0.616, TRUNC(SQRT((69.1*(39.961176-LAT))**2+(53*(-82.998794-LON))**2)*1e6)+0.535, TRUNC(SQRT((69.1*(40.760779-LAT))**2+(53*(-111.891047-LON))**2)*1e6)+0.77, TRUNC(SQRT((69.1*(43.038903-LAT))**2+(53*(-87.906474-LON))**2)*1e6)+0.617, TRUNC(SQRT((69.1*(39.103118-LAT))**2+(53*(-84.512020-LON))**2)*1e6)+0.515, TRUNC(SQRT((69.1*(29.424122-LAT))**2+(53*(-98.493628-LON))**2)*1e6)+0.641, TRUNC(SQRT((69.1*(35.052664-LAT))**2+(53*(-82.697701-LON))**2)*1e6)+0.567, TRUNC(SQRT((69.1*(27.529328-LAT))**2+(53*(-80.359040-LON))**2)*1e6)+0.548, TRUNC(SQRT((69.1*(42.963360-LAT))**2+(53*(-85.668086-LON))**2)*1e6)+0.563, TRUNC(SQRT((69.1*(36.114646-LAT))**2+(53*(-115.172816-LON))**2)*1e6)+0.839, TRUNC(SQRT((69.1*(35.467560-LAT))**2+(53*(-97.516428-LON))**2)*1e6)+0.65, TRUNC(SQRT((69.1*(33.520661-LAT))**2+(53*(-86.802490-LON))**2)*1e6)+0.63, TRUNC(SQRT((69.1*(53.962291-LAT))**2+(53*(-1.081900-LON))**2)*1e6)+0.566, TRUNC(SQRT((69.1*(36.850769-LAT))**2+(53*(-76.285873-LON))**2)*1e6)+0.544, TRUNC(SQRT((69.1*(30.267153-LAT))**2+(53*(-97.743061-LON))**2)*1e6)+0.635, TRUNC(SQRT((69.1*(36.072635-LAT))**2+(53*(-79.791975-LON))**2)*1e6)+0.518, TRUNC(SQRT((69.1*(35.261126-LAT))**2+(53*(-106.723464-LON))**2)*1e6)+0.79, TRUNC(SQRT((69.1*(38.252665-LAT))**2+(53*(-85.758456-LON))**2)*1e6)+0.529, TRUNC(SQRT((69.1*(35.149534-LAT))**2+(53*(-90.048980-LON))**2)*1e6)+0.64, TRUNC(SQRT((69.1*(30.332184-LAT))**2+(53*(-81.655651-LON))**2)*1e6)+0.561, TRUNC(SQRT((69.1*(29.951066-LAT))**2+(53*(-90.071532-LON))**2)*1e6)+0.622, TRUNC(SQRT((69.1*(42.886447-LAT))**2+(53*(-78.878369-LON))**2)*1e6)+0.514, TRUNC(SQRT((69.1*(41.667668-LAT))**2+(53*(-70.904307-LON))**2)*1e6)+0.521, TRUNC(SQRT((69.1*(41.245915-LAT))**2+(53*(-75.881308-LON))**2)*1e6)+0.577, TRUNC(SQRT((69.1*(36.985898-LAT))**2+(53*(-119.232078-LON))**2)*1e6)+0.866, TRUNC(SQRT((69.1*(34.228431-LAT))**2+(53*(-92.003196-LON))**2)*1e6)+0.693, TRUNC(SQRT((69.1*(37.227928-LAT))**2+(53*(-77.401927-LON))**2)*1e6)+0.556, TRUNC(SQRT((69.1*(42.783288-LAT))**2+(53*(-73.856562-LON))**2)*1e6)+0.532, TRUNC(SQRT((69.1*(36.153982-LAT))**2+(53*(-95.992775-LON))**2)*1e6)+0.671, TRUNC(SQRT((69.1*(31.106118-LAT))**2+(53*(-87.615178-LON))**2)*1e6)+0.686, TRUNC(SQRT((69.1*(35.960638-LAT))**2+(53*(-83.920739-LON))**2)*1e6)+0.557, TRUNC(SQRT((69.1*(26.225032-LAT))**2+(53*(-81.605586-LON))**2)*1e6)+0.571, TRUNC(SQRT((69.1*(39.758948-LAT))**2+(53*(-84.191607-LON))**2)*1e6)+0.542, TRUNC(SQRT((69.1*(38.040584-LAT))**2+(53*(-84.503716-LON))**2)*1e6)+0.541, TRUNC(SQRT((69.1*(36.178422-LAT))**2+(53*(-115.213581-LON))**2)*1e6)+0.564, TRUNC(SQRT((69.1*(37.688889-LAT))**2+(53*(-97.336111-LON))**2)*1e6)+0.678, TRUNC(SQRT((69.1*(43.594468-LAT))**2+(53*(-83.888865-LON))**2)*1e6)+0.513, TRUNC(SQRT((69.1*(37.290262-LAT))**2+(53*(-80.034269-LON))**2)*1e6)+0.573, TRUNC(SQRT((69.1*(44.266679-LAT))**2+(53*(-88.384972-LON))**2)*1e6)+0.658, TRUNC(SQRT((69.1*(32.245345-LAT))**2+(53*(-110.942688-LON))**2)*1e6)+0.789, TRUNC(SQRT((69.1*(21.306944-LAT))**2+(53*(-157.858333-LON))**2)*1e6)+0.744, TRUNC(SQRT((69.1*(42.023628-LAT))**2+(53*(-93.609745-LON))**2)*1e6)+0.679, TRUNC(SQRT((69.1*(47.658780-LAT))**2+(53*(-117.426047-LON))**2)*1e6)+0.881, TRUNC(SQRT((69.1*(37.208957-LAT))**2+(53*(-93.292299-LON))**2)*1e6)+0.619, TRUNC(SQRT((69.1*(41.252363-LAT))**2+(53*(-95.997988-LON))**2)*1e6)+0.652, TRUNC(SQRT((69.1*(41.663938-LAT))**2+(53*(-83.555212-LON))**2)*1e6)+0.547, TRUNC(SQRT((69.1*(34.000710-LAT))**2+(53*(-81.034814-LON))**2)*1e6)+0.546, TRUNC(SQRT((69.1*(43.161030-LAT))**2+(53*(-77.610922-LON))**2)*1e6)+0.538, TRUNC(SQRT((69.1*(34.730369-LAT))**2+(53*(-86.586104-LON))**2)*1e6)+0.691, TRUNC(SQRT((69.1*(43.712312-LAT))**2+(53*(-70.291508-LON))**2)*1e6)+0.5, TRUNC(SQRT((69.1*(37.083389-LAT))**2+(53*(-88.600048-LON))**2)*1e6)+0.632, TRUNC(SQRT((69.1*(32.525152-LAT))**2+(53*(-93.750179-LON))**2)*1e6)+0.612, TRUNC(SQRT((69.1*(39.840315-LAT))**2+(53*(-88.954800-LON))**2)*1e6)+0.648, TRUNC(SQRT((69.1*(43.048122-LAT))**2+(53*(-76.147424-LON))**2)*1e6)+0.555, TRUNC(SQRT((69.1*(43.073052-LAT))**2+(53*(-89.401230-LON))**2)*1e6)+0.669, TRUNC(SQRT((69.1*(26.190631-LAT))**2+(53*(-97.696103-LON))**2)*1e6)+0.636, TRUNC(SQRT((69.1*(35.045630-LAT))**2+(53*(-85.309680-LON))**2)*1e6)+0.575, TRUNC(SQRT((69.1*(31.549333-LAT))**2+(53*(-97.146670-LON))**2)*1e6)+0.625, TRUNC(SQRT((69.1*(38.863138-LAT))**2+(53*(-104.845462-LON))**2)*1e6)+0.752, TRUNC(SQRT((69.1*(41.977880-LAT))**2+(53*(-91.665623-LON))**2)*1e6)+0.637, TRUNC(SQRT((69.1*(31.758720-LAT))**2+(53*(-106.486931-LON))**2)*1e6)+0.765, TRUNC(SQRT((69.1*(32.083541-LAT))**2+(53*(-81.099834-LON))**2)*1e6)+0.507, TRUNC(SQRT((69.1*(32.298757-LAT))**2+(53*(-90.184810-LON))**2)*1e6)+0.718, TRUNC(SQRT((69.1*(30.458283-LAT))**2+(53*(-91.140320-LON))**2)*1e6)+0.716, TRUNC(SQRT((69.1*(41.684011-LAT))**2+(53*(-85.970697-LON))**2)*1e6)+0.588, TRUNC(SQRT((69.1*(36.457870-LAT))**2+(53*(-82.534128-LON))**2)*1e6)+0.531, TRUNC(SQRT((69.1*(44.699487-LAT))**2+(53*(-73.452912-LON))**2)*1e6)+0.523, TRUNC(SQRT((69.1*(32.776566-LAT))**2+(53*(-79.930922-LON))**2)*1e6)+0.519, TRUNC(SQRT((69.1*(41.506700-LAT))**2+(53*(-90.515134-LON))**2)*1e6)+0.682, TRUNC(SQRT((69.1*(34.852618-LAT))**2+(53*(-82.394010-LON))**2)*1e6)+0.545, TRUNC(SQRT((69.1*(35.385924-LAT))**2+(53*(-94.398548-LON))**2)*1e6)+0.67, TRUNC(SQRT((69.1*(40.326741-LAT))**2+(53*(-78.921970-LON))**2)*1e6)+0.574, TRUNC(SQRT((69.1*(33.882649-LAT))**2+(53*(-79.472830-LON))**2)*1e6)+0.57, TRUNC(SQRT((69.1*(37.971559-LAT))**2+(53*(-87.571090-LON))**2)*1e6)+0.649, TRUNC(SQRT((69.1*(41.282784-LAT))**2+(53*(-99.257628-LON))**2)*1e6)+0.722, TRUNC(SQRT((69.1*(30.563586-LAT))**2+(53*(-84.215223-LON))**2)*1e6)+0.53, TRUNC(SQRT((69.1*(32.107011-LAT))**2+(53*(-94.880694-LON))**2)*1e6)+0.709, TRUNC(SQRT((69.1*(39.529633-LAT))**2+(53*(-119.813803-LON))**2)*1e6)+0.811, TRUNC(SQRT((69.1*(41.079273-LAT))**2+(53*(-85.139351-LON))**2)*1e6)+0.509, TRUNC(SQRT((69.1*(41.099780-LAT))**2+(53*(-80.649519-LON))**2)*1e6)+0.536, TRUNC(SQRT((69.1*(43.614024-LAT))**2+(53*(-116.203650-LON))**2)*1e6)+0.757, TRUNC(SQRT((69.1*(43.533520-LAT))**2+(53*(-96.716093-LON))**2)*1e6)+0.725, TRUNC(SQRT((69.1*(33.473498-LAT))**2+(53*(-82.010515-LON))**2)*1e6)+0.52, TRUNC(SQRT((69.1*(42.204259-LAT))**2+(53*(-72.616201-LON))**2)*1e6)+0.543, TRUNC(SQRT((69.1*(42.732535-LAT))**2+(53*(-84.555535-LON))**2)*1e6)+0.551, TRUNC(SQRT((69.1*(40.490490-LAT))**2+(53*(-89.061189-LON))**2)*1e6)+0.675, TRUNC(SQRT((69.1*(32.656214-LAT))**2+(53*(-83.879673-LON))**2)*1e6)+0.724, TRUNC(SQRT((69.1*(32.304547-LAT))**2+(53*(-86.413341-LON))**2)*1e6)+0.698, TRUNC(SQRT((69.1*(44.246783-LAT))**2+(53*(-85.391178-LON))**2)*1e6)+0.54, TRUNC(SQRT((69.1*(32.840695-LAT))**2+(53*(-83.632402-LON))**2)*1e6)+0.503, TRUNC(SQRT((69.1*(44.052069-LAT))**2+(53*(-123.086754-LON))**2)*1e6)+0.801, TRUNC(SQRT((69.1*(46.602071-LAT))**2+(53*(-120.505899-LON))**2)*1e6)+0.81, TRUNC(SQRT((69.1*(34.844663-LAT))**2+(53*(-120.410021-LON))**2)*1e6)+0.855, TRUNC(SQRT((69.1*(30.224090-LAT))**2+(53*(-92.019843-LON))**2)*1e6)+0.642, TRUNC(SQRT((69.1*(36.600238-LAT))**2+(53*(-121.894676-LON))**2)*1e6)+0.828, TRUNC(SQRT((69.1*(35.373292-LAT))**2+(53*(-119.018713-LON))**2)*1e6)+0.8, TRUNC(SQRT((69.1*(32.642361-LAT))**2+(53*(-85.334397-LON))**2)*1e6)+0.522, TRUNC(SQRT((69.1*(44.811349-LAT))**2+(53*(-91.498494-LON))**2)*1e6)+0.702, TRUNC(SQRT((69.1*(27.800583-LAT))**2+(53*(-97.396381-LON))**2)*1e6)+0.6, TRUNC(SQRT((69.1*(35.221997-LAT))**2+(53*(-101.831297-LON))**2)*1e6)+0.634, TRUNC(SQRT((69.1*(39.757205-LAT))**2+(53*(-121.830187-LON))**2)*1e6)+0.868, TRUNC(SQRT((69.1*(34.225726-LAT))**2+(53*(-77.944710-LON))**2)*1e6)+0.55, TRUNC(SQRT((69.1*(39.961176-LAT))**2+(53*(-82.998794-LON))**2)*1e6)+0.673, TRUNC(SQRT((69.1*(45.610836-LAT))**2+(53*(-89.526737-LON))**2)*1e6)+0.705, TRUNC(SQRT((69.1*(42.271131-LAT))**2+(53*(-89.093995-LON))**2)*1e6)+0.61, TRUNC(SQRT((69.1*(39.055824-LAT))**2+(53*(-95.689019-LON))**2)*1e6)+0.605, TRUNC(SQRT((69.1*(33.215250-LAT))**2+(53*(-92.640670-LON))**2)*1e6)+0.628, TRUNC(SQRT((69.1*(38.864557-LAT))**2+(53*(-77.187759-LON))**2)*1e6)+0.604, TRUNC(SQRT((69.1*(46.720774-LAT))**2+(53*(-92.104080-LON))**2)*1e6)+0.676, TRUNC(SQRT((69.1*(42.122472-LAT))**2+(53*(-122.364104-LON))**2)*1e6)+0.813, TRUNC(SQRT((69.1*(29.985183-LAT))**2+(53*(-94.061336-LON))**2)*1e6)+0.692, TRUNC(SQRT((69.1*(33.577863-LAT))**2+(53*(-101.855167-LON))**2)*1e6)+0.651, TRUNC(SQRT((69.1*(34.603567-LAT))**2+(53*(-98.395929-LON))**2)*1e6)+0.627, TRUNC(SQRT((69.1*(51.068785-LAT))**2+(53*(-1.794472-LON))**2)*1e6)+0.576, TRUNC(SQRT((69.1*(61.218056-LAT))**2+(53*(-149.900278-LON))**2)*1e6)+0.743, TRUNC(SQRT((69.1*(42.129224-LAT))**2+(53*(-80.085059-LON))**2)*1e6)+0.516, TRUNC(SQRT((69.1*(42.499994-LAT))**2+(53*(-96.400307-LON))**2)*1e6)+0.624, TRUNC(SQRT((69.1*(33.830296-LAT))**2+(53*(-116.545292-LON))**2)*1e6)+0.804, TRUNC(SQRT((69.1*(37.424084-LAT))**2+(53*(-94.700308-LON))**2)*1e6)+0.603, TRUNC(SQRT((69.1*(31.578507-LAT))**2+(53*(-84.155741-LON))**2)*1e6)+0.525, TRUNC(SQRT((69.1*(48.232510-LAT))**2+(53*(-101.296273-LON))**2)*1e6)+0.687, TRUNC(SQRT((69.1*(31.845682-LAT))**2+(53*(-102.367643-LON))**2)*1e6)+0.633, TRUNC(SQRT((69.1*(30.267153-LAT))**2+(53*(-97.743061-LON))**2)*1e6)+0.611, TRUNC(SQRT((69.1*(39.466703-LAT))**2+(53*(-87.413909-LON))**2)*1e6)+0.581, TRUNC(SQRT((69.1*(44.801182-LAT))**2+(53*(-68.777814-LON))**2)*1e6)+0.537, TRUNC(SQRT((69.1*(37.705412-LAT))**2+(53*(-81.247014-LON))**2)*1e6)+0.559, TRUNC(SQRT((69.1*(42.098687-LAT))**2+(53*(-75.917974-LON))**2)*1e6)+0.502, TRUNC(SQRT((69.1*(40.317407-LAT))**2+(53*(-80.603549-LON))**2)*1e6)+0.554, TRUNC(SQRT((69.1*(8.994269-LAT))**2+(53*(-79.518792-LON))**2)*1e6)+0.656, TRUNC(SQRT((69.1*(30.413380-LAT))**2+(53*(-89.072958-LON))**2)*1e6)+0.746, TRUNC(SQRT((69.1*(43.628367-LAT))**2+(53*(-116.201651-LON))**2)*1e6)+0.657, TRUNC(SQRT((69.1*(44.068202-LAT))**2+(53*(-114.742041-LON))**2)*1e6)+0.758, TRUNC(SQRT((69.1*(29.651634-LAT))**2+(53*(-82.324826-LON))**2)*1e6)+0.592, TRUNC(SQRT((69.1*(32.658465-LAT))**2+(53*(-100.106850-LON))**2)*1e6)+0.662, TRUNC(SQRT((69.1*(32.692651-LAT))**2+(53*(-114.627692-LON))**2)*1e6)+0.771, TRUNC(SQRT((69.1*(46.860519-LAT))**2+(53*(-114.019501-LON))**2)*1e6)+0.762, TRUNC(SQRT((69.1*(31.323164-LAT))**2+(53*(-89.282274-LON))**2)*1e6)+0.71, TRUNC(SQRT((69.1*(45.783286-LAT))**2+(53*(-108.500690-LON))**2)*1e6)+0.756, TRUNC(SQRT((69.1*(31.223231-LAT))**2+(53*(-85.390489-LON))**2)*1e6)+0.606, TRUNC(SQRT((69.1*(39.280645-LAT))**2+(53*(-80.344534-LON))**2)*1e6)+0.598, TRUNC(SQRT((69.1*(42.252877-LAT))**2+(53*(-71.002271-LON))**2)*1e6)+0.717, TRUNC(SQRT((69.1*(43.100903-LAT))**2+(53*(-75.232664-LON))**2)*1e6)+0.526, TRUNC(SQRT((69.1*(44.080543-LAT))**2+(53*(-103.231015-LON))**2)*1e6)+0.764, TRUNC(SQRT((69.1*(42.093004-LAT))**2+(53*(-76.799587-LON))**2)*1e6)+0.565, TRUNC(SQRT((69.1*(30.226595-LAT))**2+(53*(-93.217376-LON))**2)*1e6)+0.643, TRUNC(SQRT((69.1*(35.614517-LAT))**2+(53*(-88.813947-LON))**2)*1e6)+0.639, TRUNC(SQRT((69.1*(42.370930-LAT))**2+(53*(-71.182832-LON))**2)*1e6)+0.549, TRUNC(SQRT((69.1*(38.449569-LAT))**2+(53*(-78.868916-LON))**2)*1e6)+0.569, TRUNC(SQRT((69.1*(31.311294-LAT))**2+(53*(-92.445137-LON))**2)*1e6)+0.644, TRUNC(SQRT((69.1*(46.547583-LAT))**2+(53*(-87.395595-LON))**2)*1e6)+0.553, TRUNC(SQRT((69.1*(35.842297-LAT))**2+(53*(-90.704279-LON))**2)*1e6)+0.734, TRUNC(SQRT((69.1*(36.990320-LAT))**2+(53*(-86.443602-LON))**2)*1e6)+0.736, TRUNC(SQRT((69.1*(38.029306-LAT))**2+(53*(-78.476678-LON))**2)*1e6)+0.584, TRUNC(SQRT((69.1*(27.506407-LAT))**2+(53*(-99.507542-LON))**2)*1e6)+0.749, TRUNC(SQRT((69.1*(38.958538-LAT))**2+(53*(-108.617563-LON))**2)*1e6)+0.773, TRUNC(SQRT((69.1*(43.612109-LAT))**2+(53*(-116.391513-LON))**2)*1e6)+0.711, TRUNC(SQRT((69.1*(45.670365-LAT))**2+(53*(-111.248286-LON))**2)*1e6)+0.754, TRUNC(SQRT((69.1*(34.707475-LAT))**2+(53*(-82.301773-LON))**2)*1e6)+0.647, TRUNC(SQRT((69.1*(40.416702-LAT))**2+(53*(-86.875287-LON))**2)*1e6)+0.582, TRUNC(SQRT((69.1*(47.500235-LAT))**2+(53*(-111.300808-LON))**2)*1e6)+0.755, TRUNC(SQRT((69.1*(42.562967-LAT))**2+(53*(-114.460871-LON))**2)*1e6)+0.76, TRUNC(SQRT((69.1*(44.058173-LAT))**2+(53*(-121.315310-LON))**2)*1e6)+0.821, TRUNC(SQRT((69.1*(39.266742-LAT))**2+(53*(-81.561514-LON))**2)*1e6)+0.597, TRUNC(SQRT((69.1*(40.802071-LAT))**2+(53*(-124.163673-LON))**2)*1e6)+0.802, TRUNC(SQRT((69.1*(41.876312-LAT))**2+(53*(-103.637984-LON))**2)*1e6)+0.759, TRUNC(SQRT((69.1*(31.463772-LAT))**2+(53*(-100.437038-LON))**2)*1e6)+0.661, TRUNC(SQRT((69.1*(42.833548-LAT))**2+(53*(-107.769516-LON))**2)*1e6)+0.767, TRUNC(SQRT((69.1*(44.163578-LAT))**2+(53*(-93.999400-LON))**2)*1e6)+0.737, TRUNC(SQRT((69.1*(-12.047816-LAT))**2+(53*(-77.062203-LON))**2)*1e6)+0.558, TRUNC(SQRT((69.1*(40.194754-LAT))**2+(53*(-92.583250-LON))**2)*1e6)+0.631, TRUNC(SQRT((69.1*(39.757944-LAT))**2+(53*(-94.836541-LON))**2)*1e6)+0.638, TRUNC(SQRT((69.1*(64.837778-LAT))**2+(53*(-147.716389-LON))**2)*1e6)+0.745, TRUNC(SQRT((69.1*(39.940345-LAT))**2+(53*(-82.013192-LON))**2)*1e6)+0.596, TRUNC(SQRT((69.1*(-37.471308-LAT))**2+(53*(144.785153-LON))**2)*1e6)+0.626, TRUNC(SQRT((69.1*(46.681153-LAT))**2+(53*(-68.015862-LON))**2)*1e6)+0.552, TRUNC(SQRT((69.1*(46.595806-LAT))**2+(53*(-112.027031-LON))**2)*1e6)+0.766, TRUNC(SQRT((69.1*(58.301944-LAT))**2+(53*(-134.419722-LON))**2)*1e6)+0.747, TRUNC(SQRT((69.1*(44.181377-LAT))**2+(53*(-98.365646-LON))**2)*1e6)+0.583, TRUNC(SQRT((69.1*(41.123887-LAT))**2+(53*(-100.765423-LON))**2)*1e6)+0.74, TRUNC(SQRT((69.1*(47.108611-LAT))**2+(53*(-104.710556-LON))**2)*1e6)+0.798 ) % 1 * 1000) as DMA

Efficiently select a random sample from a large data set

SELECT customer_id FROM ( SELECT DISTINCT customer_id, rownum r FROM t WHERE ora_hash(customer_id, <N>) = 0 ORDER BY ora_hash(customer_id), customer_id ) WHERE r<10000; This will pick a random 1/Nth of the customers based on the first ora_hash, sort that list randomly based on the 2nd ora_hash and pick the “first” 10K rows from there. Pick an N too low and you just end up sorting more rows than you need to. Pick an N too high and you might not get enough customer_ids to sort on. http://docs.oracle.com/cd/B13789_01/server.101/b10759/functions097.htm This is nice because calls to PL/SQL packages like dbms_random are much slower than native built-in functions like ora_hash. It’s also nice because it’s deterministic, meaning given the same input, you always get the same output. If you want to get different “random” sampling, pass ora_hash the seed_value parameter and it will change the hashing and you’ll get a different value out…. If “t” is really big you don’t even have to look at all of it if you sample it: SELECT customer_id FROM ( SELECT DISTINCT customer_id, rownum r FROM t SAMPLE BLOCK(1) WHERE ora_hash(customer_id, <N>) = 0 ORDER BY ora_hash(customer_id), customer_id ) WHERE r<10000; This means Oracle only touches 1% of the blocks in the table. IF the customer_ids are evenly distributed, this works well, however, because you often can’t control what data goes where, this begins to approach “random” as defined by somebody off the street but perhaps not the “random” as defined by a statistician.

DATETIME to DAY
TRUNC(DATETIME,'DD')

To remove punctuation from both sides of a string
RTRIM(LTRIM(string,'~!@#$%^&*()_+|}{":?><-=[]\;,./'),'~!@#$%^&*()_+|}{":?><-=[]\;,./')

Remove special characters using REGEXP_REPLACE
REGEXP_REPLACE(REPLACE(TRANSLATE(UPPER(TRIM(SEARCH_STRING)),'~!@#$%^&*()_+|}{"'':?><-=[]\;,./','#'),'#',''),'( ){1,}','_')

Even cooler way to get rid of "junk" in a string is to do it using the letters you want to keep
TRIM(LOWER(REPLACE(TRANSLATE(SEARCH_STRING,REPLACE(TRANSLATE(SEARCH_STRING, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890','#'), '#', ''),'#'), '#', ''))) REPLACE(TRANSLATE(SEARCH_STRING,REPLACE(TRANSLATE(SEARCH_STRING, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890','#'),'#',''),'#'),'#','')

Random number creation
select mod(abs(dbms_random.random),m)+n from dual; will produce a random number between n and (m+n-1) e.g. mod(abs(dbms_random.random),4)+3 will produce a random number between 3 and 6.

Create a comma delimited aggregation
CREATE TABLE test_agg (x int , y varchar2(10)) ; SELECT * FROM test_agg ; 1 a 1 b 1 c 2 aa 2 dd 3 abc 3 def 4 e 4 a SELECT x, rtrim (xmlagg (xmlelement (e, y || ',') ORDER BY y DESC).extract ('//text()'), ',') FROM test_agg GROUP BY x; 1 c,b,a 2 dd,aa 3 def,abc 4 e,a

RFM Coding

SELECT RFM_R ,RFM_F ,RFM_M ,min(R) as MIN_R ,max(R) as MAX_R ,min(F) as MIN_F ,max(F) as MAX_F ,min(M) as MIN_M ,max(M) as MAX_M FROM ( SELECT m.* ,CASE WHEN R<366 THEN NTILE(5) OVER (PARTITION BY m.RFM_R,m.RFM_F ORDER BY m.M) ELSE 0 END as RFM_M FROM ( SELECT f.* ,CASE WHEN R<366 THEN NTILE(5) OVER (PARTITION BY f.RFM_R ORDER BY f.F) ELSE 0 END as RFM_F FROM ( SELECT r.* ,CASE WHEN R<366 THEN NTILE(5) OVER (ORDER BY r.R desc) ELSE 0 END as RFM_R FROM ( SELECT b.CUSTOMER_ID ,NVL(min(TO_DATE('09/01/2012','MM/DD/YYYY')-a.ORDER_DAY),366) as R ,NVL(count(distinct a.ORDER_DAY),0) as F ,NVL(sum(a.SALES),0) as M FROM ORDERED_ITEMS a, ( SELECT CUSTOMER_ID FROM CUSTOMER_ORDERS WHERE ORDER_DAY BETWEEN TO_DATE('09/01/2012','MM/DD/YYYY')-365*2 AND TO_DATE('09/01/2012','MM/DD/YYYY') AND ORA_HASH(CUSTOMER_ID,100) = MOD(abs(dbms_random.random),100-1) GROUP BY CUSTOMER_ID ) b WHERE b.CUSTOMER_ID=a.CUSTOMER_ID(+) AND a.ORDER_DAY(+)>=TO_DATE('09/01/2012','MM/DD/YYYY')-365 AND a.ORDER_DAY(+)<=TO_DATE('09/01/2012','MM/DD/YYYY') GROUP BY b.CUSTOMER_ID ) r ) f ) m ) GROUP BY RFM_R ,RFM_F ,RFM_M ORDER BY RFM_R ,RFM_F ,RFM_M

How to determine if a text string is a number
DECODE(REGEXP_INSTR ( variable , '[^[:digit:]]'),0,'NUMBER','NOT_NUMBER') = 'NUMBER' ---A function that does the same thing: CREATE OR REPLACE function IS_NUMBER(str in varchar2) return varchar2 IS dummy number; begin dummy := TO_NUMBER(str); return ('TRUE'); Exception WHEN OTHERS then return ('FALSE'); end;

How to read in a zipcode that is character into zip5 numeric variable

,case when regexp_instr(trim(a.postal_code),'[^-0-9]')>0 then '00000' when regexp_instr(trim(a.postal_code),'[^-0-9]')=0 AND instr(trim(a.postal_code),'-')=0 AND length(trim(a.postal_code)) between 1 and 5 then lpad(trim(a.postal_code),5,'0') when regexp_instr(trim(a.postal_code),'[^-0-9]')=0 AND instr(trim(a.postal_code),'-') between 2 and 6 then lpad(substr(trim(a.postal_code),1,instr(trim(a.postal_code),'-')-1),5,'0') else '00000' end as cZIP --another way ,CASE WHEN INSTR(addr.POSTAL_CODE,'-')=0 AND LENGTH(addr.POSTAL_CODE)=7 AND DECODE(REGEXP_INSTR(addr.POSTAL_CODE,'[^[:digit:]]'),0,'NUMBER','NOT_NUMBER')='NUMBER' THEN trim(addr.POSTAL_CODE) WHEN INSTR(addr.POSTAL_CODE,'-')>0 AND LENGTH(addr.POSTAL_CODE)=8 AND DECODE(REGEXP_INSTR(SUBSTR(addr.POSTAL_CODE,1,instr(addr.POSTAL_CODE,'-')-1),'[^[:digit:]]'),0,'NUMBER','NOT_NUMBER')='NUMBER' THEN REPLACE(trim(addr.POSTAL_CODE),'-') ELSE NULL END as POSTAL_CODE

How to determine the value of another variable in a row that corresponds to the max/min of a numeric variable
SUBSTR(MAX(TO_CHAR(numeric_variable,'99999'))||other_variable),6) --using dates SUBSTR(MAX(TO_CHAR(DATETIME,'YYYYMMDDHH24MISS')||SESSION_ID),15) as MAX_SESSION_ID --using metrics ,REVERSE( SUBSTR( REVERSE( MAX(CASE WHEN GL=14 THEN COUNT_ORDERS||CHANNEL ELSE NULL END) ),1,2) ) as GL14 --Or use FIRST_VALUE ,first_value(SESSION_ID) over (ORDER BY DATETIME)

SQL That Inserts Days between Rows
SELECT b.CUSTOMER_ID, b.DAY, nvl(a.REV,0) as REV FROM ( SELECT * FROM TABLE GROUP BY CUSTOMER_ID,DAY ) a, ( SELECT CUSTOMER_ID, DAY FROM ( SELECT distinct CUSTOMER_ID FROM TABLE), ( SELECT TO_DATE('10/01/2010','MM/DD/YYYY')+rownum-1 as DAY FROM all_objects WHERE rownum <= 365+31) ) b WHERE b.DAY = a.DAY (+) AND b.CUSTOMER_ID = a.CUSTOMER_ID (+) ORDER BY b.CUSTOMER_ID, b.DAY

Back to Rick's Library