page_views.go

package main
 
import (
  "database/sql"
  "fmt"
  "math/rand"
  _ "github.com/lib/pq"
  )
 
func main() {
  db, _ := sql.Open("postgres", "user=client dbname=pipeline host=localhost port=6543 sslmode=disable")
  defer db.Close()
 
  // 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
  db.Exec(`
  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`)
 
  // Now let's simulate some page views
  for n := 0; n < 10000; n++ {
    // 10 unique urls
    url := fmt.Sprintf("/some/url/%d", (n % 10))
 
    // 1000 unique cookies
    cookie := fmt.Sprintf("%032d", (n % 1000))
 
    // latency uniformly distributed between 1 and 100
    latency := rand.Int() % 100
 
    // 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
    db.Exec(fmt.Sprintf(`
      INSERT INTO page_views (url, cookie, latency) VALUES ('%s', '%s', %d)
      `, url, cookie, latency))
  }
 
  // The output of a continuous view can be queried like any other table or view
  rows, _ := db.Query("SELECT * FROM v ORDER BY url")
  defer rows.Close()
 
  for rows.Next() {
    var url string
    var totalCount int
    var uniques int
    var p99Latency float64
    
    rows.Scan(&url, &totalCount, &uniques, &p99Latency)
    fmt.Println(fmt.Sprintf("{%s, %d, %d, %f}", url, totalCount, uniques, p99Latency))
  }
}
 
// Output:
//
// {/some/url/0, 1000, 100, 98.500000}
// {/some/url/1, 1000, 100, 99.000000}
// {/some/url/2, 1000, 100, 99.000000}
// {/some/url/3, 1000, 100, 98.000000}
// {/some/url/4, 1000, 98, 98.000000}
// {/some/url/5, 1000, 100, 99.000000}
// {/some/url/6, 1000, 100, 98.000000}
// {/some/url/7, 1000, 99, 98.500000}
// {/some/url/8, 1000, 100, 99.000000}
// {/some/url/9, 1000, 100, 99.000000}