> On 7/16/2012 4:19 PM, Luis Mochan wrote: > > What are the pros and cons of this approach vs. using a temporal table as > suggested by Keith? > My technique doesn't require a temp table so may be somewhat easier to > use. On the other hand, I suspect it might be much slower on a large table. > -- > Igor Tandetnik
First, the two queries only provide identical results if there are no duplicate values (that is, if column a does not have duplicate values). Also, the join is significantly slower even when there are relatively few rows. The first two examples use random integers between 0 and 100 for the values: Row(count_=100, avg_a=5.169999999999999, median_a=5.0, range_a=5.0, kurt_a=-0.7673160376085967, skew_a=-0.23402554873733777) Row(count_=100, avg_a=14.879999999999994, median_a=15.0, range_a=5.0, kurt_a=-1.0457937617771582, skew_a=0.1118056000984518) Row(count_=100, avg_a=25.709999999999994, median_a=25.5, range_a=5.5, kurt_a=-1.2157313706228008, skew_a=-0.09270685801695283) Row(count_=100, avg_a=35.41000000000001, median_a=35.5, range_a=4.5, kurt_a=-1.2394952074411363, skew_a=-0.004471604259021051) Row(count_=100, avg_a=45.62000000000002, median_a=45.5, range_a=5.5, kurt_a=-1.188530050739989, skew_a=-0.013766992804092316) Row(count_=100, avg_a=56.16999999999999, median_a=56.0, range_a=5.0, kurt_a=-1.2302196427237029, skew_a=-0.09962037212247572) Row(count_=100, avg_a=65.70000000000002, median_a=66.0, range_a=5.0, kurt_a=-1.109597203024603, skew_a=0.05897280879054656) Row(count_=100, avg_a=75.67000000000003, median_a=75.5, range_a=4.5, kurt_a=-0.9951976657441884, skew_a=-0.15967942413838576) Row(count_=100, avg_a=84.45, median_a=84.0, range_a=4.0, kurt_a=-1.1297545539336589, skew_a=-0.04298965686715164) Row(count_=100, avg_a=93.3, median_a=93.5, range_a=5.5, kurt_a=-1.3639028852910942, skew_a=0.15672217508773303) -------- Row(count_=110, avg_a=5.609090909090909, median_a=5.0, range_a=5.0, kurt_a=-0.8681830185663603, skew_a=-0.24511717100205904) Row(count_=93, avg_a=15.56989247311828, median_a=15.5, range_a=4.5, kurt_a=-1.222032243151948, skew_a=0.18301471536628047) Row(count_=104, avg_a=26.23076923076923, median_a=26.0, range_a=5.0, kurt_a=-1.2571959480334132, skew_a=-0.11692195963318017) Row(count_=99, avg_a=35.99999999999999, median_a=36.0, range_a=4.0, kurt_a=-1.2782872114958477, skew_a=-0.054704969902452855) Row(count_=100, avg_a=46.27999999999999, median_a=46.0, range_a=5.0, kurt_a=-1.2600599777905384, skew_a=-0.06596351047839281) Row(count_=101, avg_a=56.81188118811879, median_a=56.5, range_a=4.5, kurt_a=-1.2605815048317308, skew_a=-0.15988297188842898) Row(count_=99, avg_a=66.35353535353535, median_a=66.5, range_a=4.5, kurt_a=-1.2007449849089082, skew_a=0.10449816306964632) Row(count_=99, avg_a=76.17171717171722, median_a=76.0, range_a=4.0, kurt_a=-1.110077438783383, skew_a=-0.07135502799103619) Row(count_=98, avg_a=84.78571428571428, median_a=84.5, range_a=3.5, kurt_a=-1.2825352149279434, skew_a=-0.015783320020137475) Row(count_=97, avg_a=93.46391752577317, median_a=94.0, range_a=5.0, kurt_a=-1.391177290060431, skew_a=0.15176251761348683) For 1000 values Create Values 0.0 Temp Table 0.0160000324249 Direct Select 0.405999898911 Row(count_=1000, avg_a=4.3240000000000025, median_a=4.5, range_a=4.5, kurt_a=-1.2871745643913286, skew_a=0.08265273281471515) Row(count_=1000, avg_a=14.206999999999999, median_a=14.0, range_a=5.0, kurt_a=-1.1021251623308883, skew_a=0.06997178911239374) Row(count_=1000, avg_a=24.133000000000028, median_a=24.0, range_a=5.0, kurt_a=-1.178795322586656, skew_a=0.025951906115470408) Row(count_=1000, avg_a=34.246, median_a=34.0, range_a=5.0, kurt_a=-1.2138088064962176, skew_a=-0.002199890772421539) Row(count_=1000, avg_a=44.170999999999964, median_a=44.0, range_a=5.0, kurt_a=-1.2621090514357718, skew_a=0.028105850940342677) Row(count_=1000, avg_a=54.27799999999998, median_a=54.0, range_a=5.0, kurt_a=-1.1765353785822203, skew_a=0.028073249474787697) Row(count_=1000, avg_a=64.36300000000004, median_a=64.5, range_a=5.5, kurt_a=-1.1937063922102475, skew_a=0.022677868327259154) Row(count_=1000, avg_a=74.69400000000003, median_a=75.0, range_a=5.0, kurt_a=-1.1575622078624397, skew_a=0.011320714782724487) Row(count_=1000, avg_a=84.91999999999997, median_a=85.0, range_a=5.0, kurt_a=-1.1527905396389988, skew_a=0.03895880064672291) Row(count_=1000, avg_a=94.80699999999983, median_a=94.5, range_a=4.5, kurt_a=-1.1692517740147628, skew_a=-0.07904777702222689) -------- Row(count_=1009, avg_a=4.36570862239841, median_a=4.5, range_a=4.5, kurt_a=-1.2968951836519373, skew_a=0.07325589625519861) Row(count_=1030, avg_a=14.433980582524276, median_a=14.5, range_a=4.5, kurt_a=-1.1654893745362416, skew_a=0.06161254920508278) Row(count_=990, avg_a=24.477777777777778, median_a=24.5, range_a=4.5, kurt_a=-1.2407295816942823, skew_a=0.02204299115734132) Row(count_=992, avg_a=34.50000000000004, median_a=34.5, range_a=4.5, kurt_a=-1.261633362222406, skew_a=-0.0038751056447093443) Row(count_=990, avg_a=44.33434343434345, median_a=44.5, range_a=4.5, kurt_a=-1.2898246583479316, skew_a=0.026731188551893034) Row(count_=1008, avg_a=54.424603174603185, median_a=54.5, range_a=4.5, kurt_a=-1.2113880444515432, skew_a=0.025005384741136966) Row(count_=1084, avg_a=64.9926199261992, median_a=65.0, range_a=5.0, kurt_a=-1.2445096613484907, skew_a=0.013301745096209827) Row(count_=963, avg_a=75.5597092419521, median_a=75.5, range_a=4.5, kurt_a=-1.1958331268371774, skew_a=-0.0036949223413463026) Row(count_=982, avg_a=85.49898167006121, median_a=85.5, range_a=4.5, kurt_a=-1.2316511547563596, skew_a=0.028323605109659384) Row(count_=952, avg_a=95.04936974789908, median_a=95.0, range_a=4.0, kurt_a=-1.2147434246113913, skew_a=-0.046865868122189916) For 10000 values Create Values 0.125 Temp Table 0.0780000686646 Direct Select 42.0789999962 If you use floating point randoms between 0 and 100 (unlikley to be the same) your results are the same for the two methods. This equates to a unique constraint on the value a. Row(count_=100, avg_a=5.748706253517386, median_a=5.649503890773561, range_a=5.412757441037625, kurt_a=-1.1746133648719341, skew_a=-0.009613272016081358) Row(count_=100, avg_a=15.719252401018002, median_a=15.693584010477284, range_a=4.55963511492537, kurt_a=-1.263197835108484, skew_a=0.008040885762458427) Row(count_=100, avg_a=24.92434955218562, median_a=24.93885721975282, range_a=4.554414485957114, kurt_a=-1.3539945827984883, skew_a=0.003266018102726588) Row(count_=100, avg_a=34.099423459789975, median_a=34.563288489048965, range_a=5.018421574118731, kurt_a=-1.0587408119696673, skew_a=0.28143823756493713) Row(count_=100, avg_a=44.47208030902492, median_a=44.58815199289917, range_a=4.874941147886144, kurt_a=-1.2543233951126398, skew_a=0.2327553409751225) Row(count_=100, avg_a=55.3138398166566, median_a=54.93423317144963, range_a=5.415960481027874, kurt_a=-1.2708486071713418, skew_a=-0.14043083347876112) Row(count_=100, avg_a=65.6097929237884, median_a=65.4064175618783, range_a=4.948439437815516, kurt_a=-0.931844893881439, skew_a=-0.011890670113570125) Row(count_=100, avg_a=75.18219078190553, median_a=75.27180623430755, range_a=4.903194661190348, kurt_a=-0.9791029687782818, skew_a=0.16344862038404828) Row(count_=100, avg_a=84.50857414007987, median_a=85.12985537323614, range_a=4.87232053818542, kurt_a=-0.9942896749129843, skew_a=0.3138662037084436) Row(count_=100, avg_a=94.87824675404805, median_a=95.00796331571603, range_a=4.856222578935515, kurt_a=-0.8831448690404673, skew_a=0.06626779494219992) -------- Row(count_=100, avg_a=5.748706253517389, median_a=5.649503890773561, range_a=5.412757441037625, kurt_a=-1.1746133648718897, skew_a=-0.009613272016084752) Row(count_=100, avg_a=15.719252401018005, median_a=15.693584010477284, range_a=4.55963511492537, kurt_a=-1.2631978351200217, skew_a=0.00804088576309135) Row(count_=100, avg_a=24.92434955218562, median_a=24.93885721975282, range_a=4.554414485957114, kurt_a=-1.353994582778569, skew_a=0.0032660181014346847) Row(count_=100, avg_a=34.09942345978997, median_a=34.563288489048965, range_a=5.018421574118731, kurt_a=-1.058740812005628, skew_a=0.281438237564926) Row(count_=100, avg_a=44.47208030902493, median_a=44.58815199289917, range_a=4.874941147886144, kurt_a=-1.2543233950859998, skew_a=0.23275534097938066) Row(count_=100, avg_a=55.31383981665663, median_a=54.93423317144963, range_a=5.415960481027874, kurt_a=-1.2708486071562324, skew_a=-0.1404308334708752) Row(count_=100, avg_a=65.60979292378843, median_a=65.4064175618783, range_a=4.948439437815516, kurt_a=-0.931844892154237, skew_a=-0.01189067013875273) Row(count_=100, avg_a=75.18219078190549, median_a=75.27180623430755, range_a=4.903194661190348, kurt_a=-0.9791029673646238, skew_a=0.1634486203586964) Row(count_=100, avg_a=84.50857414007993, median_a=85.12985537323614, range_a=4.87232053818542, kurt_a=-0.9942896749150102, skew_a=0.3138662037597631) Row(count_=100, avg_a=94.87824675404813, median_a=95.00796331571603, range_a=4.856222578935515, kurt_a=-0.8831448789962583, skew_a=0.06626779504622561) For 1000 values Create Values 0.0160000324249 Temp Table 0.0 Direct Select 0.593999862671 Row(count_=1000, avg_a=5.1465713581452555, median_a=5.184817865526393, range_a=5.179990253113875, kurt_a=-1.2276078213726045, skew_a=-0.013509633447731376) Row(count_=1000, avg_a=15.477083831215271, median_a=15.496762271535907, range_a=5.116511640223681, kurt_a=-1.116214886034431, skew_a=0.014834611510481743) Row(count_=1000, avg_a=25.319340295160796, median_a=25.43882990188225, range_a=4.824704873278025, kurt_a=-1.2338857842121025, skew_a=0.030005308088103796) Row(count_=1000, avg_a=35.2228257263221, median_a=35.16963687237869, range_a=4.899809344466261, kurt_a=-1.192955688791452, skew_a=-0.02590364326826991) Row(count_=1000, avg_a=45.276240889245805, median_a=45.19651051476565, range_a=5.125692599039066, kurt_a=-1.2069413096576327, skew_a=-0.006771535776911118) Row(count_=1000, avg_a=55.26487312071604, median_a=55.27079693639841, range_a=4.936034142614691, kurt_a=-1.1637489003082986, skew_a=-0.02359354858831377) Row(count_=1000, avg_a=65.16342617092309, median_a=65.0731696358693, range_a=4.85257770137315, kurt_a=-1.1535421878609395, skew_a=-0.05481770150809203) Row(count_=1000, avg_a=75.22079115403587, median_a=75.05605132607052, range_a=5.129219516954279, kurt_a=-1.1928080062114332, skew_a=-0.11017049933950125) Row(count_=1000, avg_a=85.08982592318694, median_a=85.03079660153368, range_a=4.845310277204703, kurt_a=-1.1605147433307423, skew_a=0.006264076988118132) Row(count_=1000, avg_a=94.84622180234878, median_a=94.93777960062039, range_a=5.058983484628392, kurt_a=-1.1365822005394108, skew_a=0.09433749833452731) -------- Row(count_=1000, avg_a=5.146571358145253, median_a=5.184817865526393, range_a=5.179990253113875, kurt_a=-1.2276078213725388, skew_a=-0.013509633447758265) Row(count_=1000, avg_a=15.477083831215293, median_a=15.496762271535907, range_a=5.116511640223681, kurt_a=-1.116214886032956, skew_a=0.014834611511003215) Row(count_=1000, avg_a=25.3193402951608, median_a=25.43882990188225, range_a=4.824704873278025, kurt_a=-1.2338857841920354, skew_a=0.03000530808686081) Row(count_=1000, avg_a=35.222825726322036, median_a=35.16963687237869, range_a=4.899809344466261, kurt_a=-1.19295568839722, skew_a=-0.025903643288459458) Row(count_=1000, avg_a=45.27624088924583, median_a=45.19651051476565, range_a=5.125692599039066, kurt_a=-1.2069413097957296, skew_a=-0.006771535764919865) Row(count_=1000, avg_a=55.26487312071602, median_a=55.27079693639841, range_a=4.936034142614691, kurt_a=-1.163748900249751, skew_a=-0.023593548599805832) Row(count_=1000, avg_a=65.16342617092316, median_a=65.0731696358693, range_a=4.85257770137315, kurt_a=-1.1535421837748807, skew_a=-0.05481770158063221) Row(count_=1000, avg_a=75.22079115403582, median_a=75.05605132607052, range_a=5.129219516954279, kurt_a=-1.1928079995789285, skew_a=-0.11017049944591531) Row(count_=1000, avg_a=85.08982592318682, median_a=85.03079660153368, range_a=4.845310277204703, kurt_a=-1.16051473436529, skew_a=0.006264076855812619) Row(count_=1000, avg_a=94.84622180234877, median_a=94.93777960062039, range_a=5.058983484628392, kurt_a=-1.1365821843686328, skew_a=0.09433749811719974) For 10000 values Create Values 0.171999931335 Temp Table 0.0940001010895 Direct Select 51.2660000324 A unique index on a speeds things up quite a bit: Row(count_=1000, avg_a=5.291284584219622, median_a=5.224169743431317, range_a=5.204884398565512, kurt_a=-1.2053491612307186, skew_a=-0.06412961915003139) Row(count_=1000, avg_a=15.19960706219981, median_a=15.258680066627072, range_a=4.828785540278402, kurt_a=-1.1911058694354817, skew_a=0.02172873337115814) Row(count_=1000, avg_a=25.379367514831557, median_a=25.23120484027688, range_a=5.129646711684137, kurt_a=-1.2205358474609407, skew_a=-0.10520925819639537) Row(count_=1000, avg_a=35.232784327212876, median_a=35.27068212562956, range_a=4.897347967780343, kurt_a=-1.2511839407074996, skew_a=0.02777253700475593) Row(count_=1000, avg_a=45.25627699519564, median_a=45.25291206568618, range_a=5.074668715797891, kurt_a=-1.1717152124275125, skew_a=0.005573021978287459) Row(count_=1000, avg_a=55.335118379673474, median_a=55.34391141689564, range_a=5.0157589008802965, kurt_a=-1.2160223340828873, skew_a=-0.019172469125908895) Row(count_=1000, avg_a=65.56019159339326, median_a=65.44314456692297, range_a=5.070373293357882, kurt_a=-1.1278868688217012, skew_a=-0.059575523737229547) Row(count_=1000, avg_a=75.47890939134653, median_a=75.38768879622768, range_a=4.863739620288946, kurt_a=-1.2082910998919425, skew_a=-0.027338707469408953) Row(count_=1000, avg_a=84.92926963544527, median_a=85.05409603311779, range_a=4.796697574458776, kurt_a=-1.218065301763463, skew_a=0.0457076356747131) Row(count_=1000, avg_a=94.76523595655303, median_a=94.93062523363062, range_a=5.064303290298284, kurt_a=-1.161171335243679, skew_a=0.04271147510153828) -------- Row(count_=1000, avg_a=5.291284584219632, median_a=5.224169743431317, range_a=5.204884398565512, kurt_a=-1.2053491612307055, skew_a=-0.06412961915002825) Row(count_=1000, avg_a=15.199607062199814, median_a=15.258680066627072, range_a=4.828785540278402, kurt_a=-1.1911058694392072, skew_a=0.0217287333718333) Row(count_=1000, avg_a=25.37936751483154, median_a=25.23120484027688, range_a=5.129646711684137, kurt_a=-1.2205358474341614, skew_a=-0.10520925819810698) Row(count_=1000, avg_a=35.23278432721286, median_a=35.27068212562956, range_a=4.897347967780343, kurt_a=-1.2511839408600005, skew_a=0.027772537009706603) Row(count_=1000, avg_a=45.25627699519566, median_a=45.25291206568618, range_a=5.074668715797891, kurt_a=-1.1717152125360895, skew_a=0.005573021987383835) Row(count_=1000, avg_a=55.3351183796735, median_a=55.34391141689564, range_a=5.0157589008802965, kurt_a=-1.2160223337933451, skew_a=-0.019172469121147794) Row(count_=1000, avg_a=65.56019159339328, median_a=65.44314456692297, range_a=5.070373293357882, kurt_a=-1.1278868716167605, skew_a=-0.059575523710255075) Row(count_=1000, avg_a=75.47890939134649, median_a=75.38768879622768, range_a=4.863739620288946, kurt_a=-1.208291094127802, skew_a=-0.027338707557266136) Row(count_=1000, avg_a=84.92926963544532, median_a=85.05409603311779, range_a=4.796697574458776, kurt_a=-1.218065302852724, skew_a=0.045707635738103734) Row(count_=1000, avg_a=94.76523595655297, median_a=94.93062523363062, range_a=5.064303290298284, kurt_a=-1.1611713333416376, skew_a=0.04271147504423989) For 10000 values Create Values 0.171999931335 Temp Table 0.0629999637604 Direct Select 25.8129999638 The temp table with even 1 million values is still pretty quick (these are ints with no index) Row(count_=100000, avg_a=4.493870000000015, median_a=4.5, range_a=4.5, kurt_a=-1.2220637501418172, skew_a=-0.001574985987403867) Row(count_=100000, avg_a=14.511620000000136, median_a=14.5, range_a=5.5, kurt_a=-1.216483063530485, skew_a=0.0016051623831945957) Row(count_=100000, avg_a=24.52255999999982, median_a=25.0, range_a=5.0, kurt_a=-1.2176495314446354, skew_a=0.00462555887725628) Row(count_=100000, avg_a=34.52627999999973, median_a=35.0, range_a=5.0, kurt_a=-1.2264948070116772, skew_a=-0.0032866643107762328) Row(count_=100000, avg_a=44.52893000000028, median_a=45.0, range_a=5.0, kurt_a=-1.2086115586426338, skew_a=-0.000191082937635113) Row(count_=100000, avg_a=54.51072000000024, median_a=55.0, range_a=5.0, kurt_a=-1.2237607773475694, skew_a=0.0011560871219098848) Row(count_=100000, avg_a=64.48885999999983, median_a=64.5, range_a=4.5, kurt_a=-1.2252413806054188, skew_a=-0.000483154638792341) Row(count_=100000, avg_a=74.4663800000003, median_a=74.0, range_a=5.0, kurt_a=-1.2185013767429773, skew_a=-0.0005112597607989715) Row(count_=100000, avg_a=84.45591999999942, median_a=84.0, range_a=5.0, kurt_a=-1.212945093586538, skew_a=-2.30809369187508e-05) Row(count_=100000, avg_a=94.50174000000081, median_a=94.0, range_a=5.0, kurt_a=-1.2288439835751905, skew_a=-0.010769765167932835) For 1000000 values Create Values 13.0629999638 Temp Table 10.9850001335 --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
