full_join.rb

gistfile1.rb

require 'sequel'
require 'active_support/core_ext'
DB = Sequel.connect(:adapter  => 'jdbc',
  :driver   => 'com.microsoft.sqlserver.jdbc.SQLServerDriver',
  :username => 'read_only',
  :password => 'read_only',
  :uri      => 'XXXX')

p 'connecting'

today = Date.parse("2011-03-03")
yesterday = today - 2
date_range = yesterday...today

visit_ds = (DB[:summarized_buyclicks].
            join(:linksource_types_and_groups, :id => :linksource_id).
            filter(:created_at => date_range).
            exclude(:group_id => 24).
            select{[:linksource_visit_id, max(:group).as(:group), sum(:commission).as(:commission)]}.
            group(:linksource_visit_id))

visitor_ds = (DB[:linksource_visits].
              join(visit_ds.as(:x), :linksource_visit_id => :id).
              select(:visitor_id, :x__group, :x__commission).
              order(:visitor_id, :created_at, :id))

another_visitor_ds = DB[:linksource_visits].
               join(:paths, :id => :linksource_visits__path_id).
               join(:linksource_types_and_groups.as(:ls), :id => :linksource_visits__linksource_id).
               join(:buyclicks, :linksource_visit_id => :linksource_visits__id).
               left_join(:transactions, :order_id => :buyclicks__id).
               filter(:buyclicks__created_at => date_range).
               exclude(:group_id => 24).
               select{[
                 :linksource_visit_id.as(:visitor_id),
                 #min(:linksource_visits__created_at).as(:created_at),
                 max(:group).as(:group),
                 sum(:commission).as(:commission)
               ]}.
               group(:linksource_visit_id)

p visitor_ds.count
p another_visitor_ds.count
p visitor_ds.sql
p another_visitor_ds.sql

start_time = Time.now
p visitor_ds.first
end_time = Time.now
process_time_visitor_ds = end_time - start_time
p "visitor_ds process time: " + process_time_visitor_ds.to_s

start_time = Time.now
p another_visitor_ds.first
end_time = Time.now
process_time_another_visitor_ds = end_time - start_time
p "another_visitor_ds process time: " + process_time_another_visitor_ds.to_s