Sunday, July 28, 2013

Learn You Some SQL For Great Good - Unlearn What You Have Learned OR How Select Logically Works (the HAVING clause)

"O Cassius, Brutus gave the word too early,
Who, having some advantage on Octavius,
Took it too eagerly; his soldiers fell to spoil,
Whilst we by Antony are all enclosed."
-- Shakespeare, Julius Caesar
Act V, Scene III, Lines 5-8

HAVING and a group


Next on our trip down the SELECT we find ourselves at HAVING.

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. WITH CUBE or WITH ROLLUP
  7. HAVING
  8. SELECT
  9. DISTINCT
  10. ORDER BY
  11. TOP
HAVING can best be thought of as a WHERE clause for the GROUP BY.  HAVING applies a filter on the groups created by the GROUP BY clause.

A common need is to find when you have more than one of something in your data set.


SELECT
   a.name AS playwright
  ,COUNT(*) AS number_of_plays
  FROM plays AS p
  INNER JOIN playwrights AS a
    ON p.playwrightId = a.id
  WHERE a.birthEra = 'AD'
  GROUP BY a.name
  HAVING COUNT(*) > 1
;

(on SQL Fiddle)

By using the HAVING clause we are able to filter on the groups that the GROUP BY gives us, in the above we filter out the playwrights that have less than 1 play in our data set.  This filtering takes place after the filtering we do with the INNER JOIN (which gets rid of Jacopone da Todi) and the WHERE clause filtering which gets rid of the ancient playwrights.

Given the data set below.

CREATE TABLE plays (
  id [int] IDENTITY(1,1)
 ,name [varchar](100)
 ,playwrightId [int]
);

 CREATE TABLE playwrights (
  id [int] IDENTITY(1,1)
 ,name [varchar](100)
 ,birthYear [int]
 ,birthEra [char](2)
);

 INSERT INTO playwrights
(name, birthYear, birthEra)
VALUES
  ('Oscar Wilde', 1854, 'AD')
 ,('Euripides', 480, 'BC')
 ,('Aeschylus', 525, 'BC')
 ,('Sophocles', 497, 'BC')
 ,('William Shakespeare', 1564, 'AD')
 ,('Jacopone da Todi', 1230, 'AD')
;

 INSERT INTO plays
(name, playwrightId)
VALUES
  ('The Importance of Being Earnest', 1)
 ,('Medea', 2)
 ,('Women of Troy', 2)
 ,('Orestes', 2)
 ,('Phoenician Women', 2)
 ,('Bacchae', 2)
 ,('Seven against Thebes', 3)
 ,('Agamemnon', 3)
 ,('The Libation Bearers', 3)
 ,('The Eumenides', 3)
 ,('Prometheus Bound', 3)
 ,('Oedipus Tyrannus', 4)
 ,('Oedipus at Colonus', 4)
 ,('Antigone', 4)
 ,('Electra', 4)
 ,('The Tempest', 5)
 ,('A Midsummer Night''s Dream', 5)
 ,('The Merchant of Venice', 5)
 ,('As You Like It', 5)
 ,('Romeo and Juliet', 5)
 ,('Julius Caesar', 5)
 ,('Macbeth', 5)
 ,('Hamlet', 5)
 ,('Othello', 5)
 ,('The Second Shepherds'' Play', 0)
 ,('Edward III', 0)
;

(on SQL Fiddle)
Posted by
Labels: , ,

Learn You Some SQL For Great Good - Unlearn What You Have Learned OR How Select Logically Works (the WITH CUBE or WITH ROLLUP clause)

"They came upon me as a wide breaking in of waters:
in the desolation they rolled themselves upon me."
-- Job 30:14

We are now onto WITH CUBE and WITH ROLLUP in our trip down the SELECT clause.

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. WITH CUBE or WITH ROLLUP
  7. HAVING
  8. SELECT
  9. DISTINCT
  10. ORDER BY
  11. TOP
I would be lying if I say I have ever used these.  Instead of bsing my way through them, I will instead give links to others that seem to know what they are talking about.  At sometime if I actually use them I may come back and put actual content.

Pinal Dave is one of the first people I look to when I need to know something about SQL Server here is a link to his  blog on WITH ROLLUP: http://blog.sqlauthority.com/2010/02/24/sql-server-introduction-to-rollup-clause/

Craig Freedman has a blog entry on WITH ROLLUP: http://blogs.msdn.com/b/craigfr/archive/2007/09/21/aggregation-with-rollup.aspx and another on WITH CUBE: http://blogs.msdn.com/b/craigfr/archive/2007/09/27/aggregation-with-cube.aspx
Posted by
Labels: , ,

Sunday, July 21, 2013

Learn You Some SQL For Great Good - Unlearn What You Have Learned OR How Select Logically Works (the GROUP BY clause)

"A gang of cynics can upset a whole city;
a group of sages can calm everyone down"
-- Proverbs 29:8

Grouping by Groups


We are now onto GROUP BY in our trip down the SELECT clause.

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. WITH CUBE or WITH ROLLUP
  7. HAVING
  8. SELECT
  9. DISTINCT
  10. ORDER BY
  11. TOP

Counting


As Celia said to Rosalind, "It is as easy to count atomies as to resolve the propositions of a lover" (As You Like It, Act III, Scene II, Lines 225-226).  Counting is one of those skills that you use through out your life, in many different ways and for many different reason.  SQL provides the means to count members of sets through use of the COUNT aggregate.

If we just wanted to get the total number of records we could just use the following:



SELECT COUNT(*)
  FROM plays
;

This would give use the total number of plays in our set (26 in the set we have been working with).

It would be useful if we could apply an aggregate like to groups of object so that we get things like the total count of plays for each playwright in our database.  Luckily SQL give us the GROUP BY clause to solve just this kind of problem.

The GROUP BY takes the data given to it by the FROM and WHERE clauses and creates sets of data based on attribute it is given to group on.  Say we wanted to get the total number of plays for each playwright in our database.  We can use the following SQL to get this count:



SELECT
   a.name AS playwright
  ,COUNT(*) AS number_of_plays
  FROM plays AS p
  INNER JOIN playwrights AS a
    ON p.playwrightId = a.id
  GROUP BY a.name
;

In the query above each play is associated to its playwright, this set of data is given to the GROUP BY which is going to create data sets based on whom the playwright is for the play.  For each one of these sets from the GROUP BY clause, we are going to get the name of the playwright and the COUNT of the number of plays they have in our database.

If we want to we could get just the number of plays each playwright that was born in AD has using the following:



SELECT
   a.name AS playwright
  ,COUNT(*) AS number_of_plays
  FROM plays AS p
  INNER JOIN playwrights AS a
    ON p.playwrightId = a.id
  WHERE a.birthEra = 'AD'
  GROUP BY a.name
;

This will just give use the number of plays for Oscar Wilde and William Shakespeare because the WHERE clause is applied to the data set before the GROUP BY gets the data set, so the GROUP BY only sees Oscar Wilde and William Shakespeare.  This is similar to why what is given to the SELECT statement outside of an aggregate function like COUNT, must be in the GROUP BY clause, otherwise it is not available to the SELECT, but we getting a head of ourselves.

As always the SQL examples can be found on SQL Fiddle.


Posted by
Labels: , ,

Sunday, July 14, 2013

Closure on Closures (examples in Javascript)

"Within the guilty closure of thy walls
Richard the Second here was hacked to death;
And, for more slander to thy dismal seat,

We give to thee our guiltless blood to drink."
--Shakespeare, Richard III
Act III, Scene III, Lines 10-13

Question: "What is a closure?"




A closure is made up of the following

  • function
  • environment

Example with just Function and Environment



function makeHello() {
    var text = 'Hello';
    return function() {
        alert(text);
    }
}

function noFree() {
    var hello = makeHello();
    hello();
}


In the Javascript above, the function is the anonymous function returned from makeHello(), the environment is variable text with its value of Hello.

  • function => anonymous function returned from makeHello()
  • environment => variable text

Example with just Function and Environment, but more interesting


function makeCounter() {
    var counter = 0;
    return function() {
        alert(counter);
        counter++;
    }
}

var counter = makeCounter();
function counter() {
    counter();
}

In the Javascript above, the function is the anonymous function returned from makeCounter(), the environment is variable counter  with its value of 0 which incremented on each call to the anonymous function.

  • function => anonymous function returned from makeCounter()
  • environment => variable counter
Each call to anonymous function returned will give back a different value of counter.

var counter = makeCounter();
function counterTwice() {
    counter();  //0
    counter();  //1
}

Question: "What is a closure?"

Answer: "A poor man's class"


The two examples above do not give us anything we could not get with a class.

Example with a Free Variable


function makeAdder(toAdd) {
    return function(value) {
        return toAdd + value;
    }
}

In the Javascript above, the function is the anonymous function returned from makeAdder()and the environment is variable toAdd.

  • function => anonymous function returned from makeAdder()
  • environment => variable toAdd
Once the anonymous function is made the toAdd value is bound to the value passed in to the makeAdder() function, this value cannot be changed, but value the bound variable can be change on every call to the anonymous function.

var add5 = makeAdder(5);
function add5and2() {
    alert(add5(2)); //7
}
function add5and4() {
    alert(add5(4)); //9
}

Question: "What is a closure?"

Answer: "A function that can access non-local variables"


The example above should us how to use a closure to access non-local variables.

Example with a "Cache" and a FizzBuzz function


function makeFizzBuzz() {
    var map = {};
    return function(value) {
        if (map[value])
            return 'from mapper: ' + map[value];
        
        if (value % 15 === 0) {
            map[value] = 'FizzBuzz';
            return 'FizzBuzz';
        }
        else if (value % 3 === 0) {
            map[value] = 'Fizz';
            return 'Fizz';
        }
        else if (value % 5 === 0) {
            map[value] = 'Buzz';
            return 'Buzz';
        }
        else {
            map[value] = value.toString();
            return value.toString();
        }
    }
}

In the Javascript above, the function is the anonymous function returned from makeFizzBuzz()and the environment is dictionary map.

  • function => anonymous function returned from makeFizzBuzz()
  • environment => dictionary map
Once the anonymous function is made the dictionary map is bound to the function creating the environment that the anonymous function is executed in, the bound variable value can be change on every call to the anonymous function.  When the anonymous function is called a look up is done against the map, if the value is found it is returned to the caller, if it is not found the result is calculated and added to map and then returned to the caller, thus the environment in which the anonymous function is executing in is being enhanced with new information added to the dictionary.

function testFizzBuzzWith3() {
    var fizzBuzzer = makeFizzBuzz();
    alert(fizzBuzzer(3));
}

function testFizzBuzzFull() {
    var fizzBuzzer = makeFizzBuzz();
    alert('3 should be Fizz = ' + fizzBuzzer(3));
    alert('5 should be Buzz = ' + fizzBuzzer(5));
    alert('15 should be FizzBuzz = ' + fizzBuzzer(15));
    alert('1 should be 1 = ' + fizzBuzzer(1));
    alert('15 should be FizzBuzz from mapper = ' + fizzBuzzer(15));
    alert('9 should be Fizz = ' + fizzBuzzer(9));
    alert('25 should be Buzz = ' + fizzBuzzer(25));
    alert('30 should be FizzBuzz = ' + fizzBuzzer(30));
}

We see above that once the value 15 is passed to the anonymous function a later call to the same function with the value of 15 will be found in the dictionary.  This is known as the memorization pattern (other examples in F# or Haskell).

Question: "What is a closure?"

Answer: "A function that captures the environment in which it was created"

We see more fully now by the above example that a closure captures the environment in which it was created.

Examples are all on jsFiddle.

These site have really great examples:
Posted by
Labels: ,

Saturday, July 6, 2013

Learn You Some SQL For Great Good - Unlearn What You Have Learned OR How Select Logically Works (the WHERE clause--NULL)

"We hold the promise, who shall make it null? If God be for us, who can be against us?"
-- Saint Augustine
The Confessions of Saint Augustine, Book XII

Who Could Make It NULL?


We are going to cover one last thing before we are done with the WHERE clause in the SELECT statement.

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. WITH CUBE or WITH ROLLUP
  7. HAVING
  8. SELECT
  9. DISTINCT
  10. ORDER BY
  11. TOP

I Don't Know


A criticism of popular governments is that people felt that they should always have an opinion on everything.  Often it is OK to simply say "I don't know".  An unknown represents a missing or unexplored part.



On old maps, the unknown sections of the map would have the caption, here be dragons.

In SQL an unknown is represented by a value of NULL.  We have already seen NULL values when we were doing LEFT OUTER and FULL OUTER JOINs.  Those places where we do not have a related value across the tables, those values came back as NULL, meaning unknown.

Working with the Unknown


In SQL NULL does not equal NULL, meaning that an unknown value does not equal an unknown value (which makes sense if you really think about it).  This is important, since if you have NULL values you need to use the special IS NULL operator to check if a value is NULL.

Here is some SQL (that has not been full gone over yet) to show the point:

SELECT 
  CASE
    WHEN NULL = NULL THEN 'NULL = NULL'
    ELSE 'NULL <> NULL'
  END AS NullEqual
 ,CASE
    WHEN NULL IS NULL THEN 'NULL IS NULL'
    ELSE 'NULL IS NOT NULL'
  END AS NullIsNull
;

This will give us the output: NULL <> NULL (meaning NULL does not equal NULL) and NULL IS NULL (meaning NULL has the value of NULL).

(on SQL Fiddle)

Data Quality is Job Number 1


Say that you want to be able to get the values in one table that do not link to the values in another table, how would you do that?


SELECT
   p.name AS play
  FROM plays AS p
  LEFT JOIN playwrights AS a
    ON p.playwrightId = a.id
  WHERE a.id IS NULL
;

(on SQL Fiddle)

The WHERE clause on the query would filter off all the plays which have known playwrights leaving us with just The Second Sheperd's Play and Edward III.

Likewise we can use this pattern to find all the playwrights without known plays.


SELECT
   a.name AS playwright
  FROM playwrights AS a
  LEFT JOIN plays AS p
    ON p.playwrightId = a.id
  WHERE p.playwrightId IS NULL
;

(on SQL Fiddle)

This will give us just the playwright Jacopone da Todi, who has no known plays.
Posted by
Labels: , ,
Subscribe to: Posts (Atom)