page_views.rb

require 'pg'
pipeline = PGconn.connect("dbname='pipeline' user='client' host='localhost' port=6543")

# This continuous view will perform 3 aggregations on page view traffic, grouped by url:
#
# total_count - count the number of total page views for each url
# uniques     - count the number of unique users for each url
# p99_latency - determine the 99th-percentile latency for each url

q = "" +
"CREATE CONTINUOUS VIEW v AS " \
"SELECT " \
"  url::text, " \
"  count(*) AS total_count, " \
"  count(DISTINCT cookie::text) AS uniques, " \
"  percentile_cont(0.99) WITHIN GROUP (ORDER BY latency::integer) AS p99_latency " \
"FROM page_views GROUP BY url"
pipeline.exec(q)

for n in 1..10000 do
  # 10 unique urls
  url = '/some/url/%d' % (n % 10)

  # 1000 unique cookies
  cookie = '%032d' % (n % 1000)

  # latency uniformly distributed between 1 and 100
  latency = rand(101)

  # NOTE: it would be much faster to batch these into a single INSERT
  # statement, but for simplicity's sake let's do one at a time
  pipeline.exec(
    "INSERT INTO page_views (url, cookie, latency) VALUES ('%s', '%s', %d)" % [url, cookie, latency])
end

# The output of a continuous view can be queried like any other table or view
rows = pipeline.exec('SELECT * FROM v ORDER BY url')

rows.each do |row|
  puts row
end

# Clean up
pipeline.exec('DROP CONTINUOUS VIEW v')

# Output:
#
# {"url"=>"/some/url/0", "total_count"=>"1000", "uniques"=>"100", "p99_latency"=>"100"}
# {"url"=>"/some/url/1", "total_count"=>"1000", "uniques"=>"100", "p99_latency"=>"100"}
# {"url"=>"/some/url/2", "total_count"=>"1000", "uniques"=>"100", "p99_latency"=>"99.55"}
# {"url"=>"/some/url/3", "total_count"=>"1000", "uniques"=>"100", "p99_latency"=>"99"}
# {"url"=>"/some/url/4", "total_count"=>"1000", "uniques"=>"98", "p99_latency"=>"99.05"}
# {"url"=>"/some/url/5", "total_count"=>"1000", "uniques"=>"100", "p99_latency"=>"100"}
# {"url"=>"/some/url/6", "total_count"=>"1000", "uniques"=>"100", "p99_latency"=>"100"}
# {"url"=>"/some/url/7", "total_count"=>"1000", "uniques"=>"99", "p99_latency"=>"99.05"}
# {"url"=>"/some/url/8", "total_count"=>"1000", "uniques"=>"100", "p99_latency"=>"100"}
# {"url"=>"/some/url/9", "total_count"=>"1000", "uniques"=>"100", "p99_latency"=>"99"}