andrewscaya

<?php

use Zend\Db\Sql\Select;

// basic table
$select0 = new Select;
$select0->from('foo');
// 'SELECT "foo".* FROM "foo"';

andrewscaya

<?php

/*
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,

andrewscaya

CREATE TABLE example (
  id INTEGER PRIMARY KEY NOT NULL,
  data jsonb
);

INSERT INTO example (id, data) VALUES
(1, '{"name": "Paint house", "tags": ["Improvements", "Office"], "finished": true}'),
(2, '{"name": "Wash dishes", "tags": ["Clean", "Kitchen"], "finished": false}'),
(3, '{"name": "Cook lunch", "tags": ["Cook", "Kitchen", "Tacos"], "ingredients": ["Tortillas", "Guacamole"], "finished": false}'),
(4, '{"name": "Vacuum", "tags": ["Clean", "Bedroom", "Office"], "finished": false}'),

andrewscaya

SELECT 
  SUM(total) as total,
  SUM(CASE WHEN collected IS TRUE THEN total END) as collected,
  year
 FROM
  invoices
GROUP BY
  year

andrewscaya

SELECT
  event_id,
  date - lag(date) OVER (PARTITION BY event_id ORDER BY date) as difference
FROM
  events;

andrewscaya

SELECT
  day,
  month,
  year,
  sum(total)
FROM 
  revenue
GROUP BY GROUPING SETS (
  (day, month, year),
  (month, year),

andrewscaya

 SELECT 
   d.id,
   d.name,
   e_info.avg_salary,
   e_info.num_employees
FROM department d,
       LATERAL (SELECT 
            AVG(e.salary) AS avg_salary,
            COUNT(*) AS num_employees
           FROM employees e

andrewscaya

WITH RECURSIVE tree_list AS (
   SELECT 
     id, item, parent_id, CAST(item AS varchar(1000)) AS path
  FROM
    tree
  WHERE parent_id IS NULL
  UNION ALL
  SELECT
    child.id, child.item, child.parent_id, CAST(parent.path || '->' || child.item As varchar(1000)) AS path
  FROM

andrewscaya

<?php

/**
 * @param double $cReal
 * @param double $cImag
 * @param int $maxIterations
 * @return int
 */
function mandelbrotIteration($cReal, $cImag, $maxIterations)
{

andrewscaya

Latency Comparison Numbers
--------------------------
L1 cache reference                           0.5 ns
Branch mispredict                            5   ns
L2 cache reference                           7   ns                      14x L1 cache
Mutex lock/unlock                           25   ns
Main memory reference                      100   ns                      20x L2 cache, 200x L1 cache
Compress 1K bytes with Zippy             3,000   ns        3 us
Send 1K bytes over 1 Gbps network       10,000   ns       10 us
Read 4K randomly from SSD*             150,000   ns      150 us          ~1GB/sec SSD