Posts

Showing posts from December, 2013

mysql tips - HAVING - another clause use to filter result like WHERE

Example: correct usage: SELECT c1, count(*) from t1 where Date like "2013-12-30 %" group by c1 HAVING count(*)>2 order by c1; wrong usage: SELECT c1, count(*) from t1 where Date like "2013-12-30 %" and count(*)>2  group by c1 order by c1; Because the aggregate function can not be used in WHERE clause. WHERE clause is used to SELECT the rows. And aggregate functions are after WHERE, and before HAVING. HAVING clause is used to filter the select result.

Google Analytics track in ruby on Rails application

There are several ways/language  to track your website. Here we discuss how to use it is ruby project. Solution 1: In Ruby, you can put the script below in the specific webpage to track only that page: rubyproject/app/views/webpagename/show.html.haml file :javascript   var gaJsHost = (("https:" == document.location.protocol) ? "https://ssl." : "http://www.");   document.write(unescape("%3Cscript src='" + gaJsHost + "google-analytics.com/ga.js' type='text/javascript'%3E%3C/script%3E")); :javascript   try{     var pageTracker = _gat._getTracker("$GOOGLE_ANALYTICS_ID");     pageTracker._trackPageview();   } catch(err) {} Solution 2: If you want to track all your webpages,  Step 1 : create a new haml file in the path: app/views/shared/_google_analytics.html.haml -For the local usage, development environment, in this _google_analytics.html.haml file:  :javascript   var _gaq = _gaq ...

mysql tips - INSERT ... SELECT..., a complex usage with "SELECT" a join table

Mysql can be as powerful as possible to process the data in all your tables, you just need to be patient to figure it out the way to combine all the functions/cmds together. Recently, I have a chance to experience this. This is base on the MYSQL cmd INSERT ... SELECT ... ON DUPLICATE KEY UPDATE usage, and it is possible that the SELECT part can be as complex as possible. INSERT t3name (DateTime, t3c1, t3c2, t3c3, t3data ) SELECT CONCAT(date(t1.Date),' ',hour(t1.Date),':00:00') AS DateTime, t1.c1, t1.c2, t1.c3,                     count(t1.c4) AS  t3data  FROM table2name t2        INNER JOIN table1name t1 on t2.c2=t1.c3 and t2.c3=t1.4        WHERE t1.Date like "2013-12-10 %" and t2.Date like "2013-12-10 %"        GROUP BY date(t1.Date), hour(t1.Date), t1.c2, t1.c3, t1.c4        ORDER BY t1.c2  ON DUPLICATE KEY UPDATE t3name. t3data = ...