Criando Ranks no Postgres usando Window Functions

Vamos supor que você tenha a seguinte tabela:

E você tenha que ordená-la de acordo com os seguintes critérios: a maior média por departamento, seguido de maior nota a, maior nota b e finalmente maior nota c. Fica bem simples se usarmos a cláusula order by:


SELECT department, name, grade_a, grade_b, grade_c
FROM candidates
ORDER BY department, ((grade_a + grade_b + grade_c) / 3) DESC,
grade_a DESC, grade_b DESC, grade_c DESC


Resultado:

Mas agora queremos criar uma qualificação indicando a posição do candidato de acordo com os critérios acima e esta posição deve ser relativa ao departamento que ele pertence, ou seja queremos saber os trê melhores qualificados no departamento "Development" e os três melhores no departamento "Marketing". Talvez muitos já estejam pensando em usar sua linguagem de programação favorita para resolver este problema mas no Postgres temos as Window Functions:


SELECT department, name, grade_a, grade_b, grade_c,
rank() OVER(PARTITION BY department ORDER BY ((grade_a + grade_b + grade_c) / 3) DESC, grade_a DESC, grade_b DESC, grade_c DESC)
FROM candidates


Resultado:

Legal, agora temos um número indicando a posição de cada candidato relativo ao seu departamento. Mas digamos agora que apenas os dois melhores candidatos de cada departamento devem ser retornados. Neste caso a seguinte query deve ser satisfatória:


SELECT *
FROM
(SELECT department, name, grade_a, grade_b, grade_c,
rank() OVER(PARTITION BY department ORDER BY ((grade_a + grade_b + grade_c) / 3) DESC, grade_a DESC, grade_b DESC, grade_c DESC)
FROM candidates) AS sub_query
WHERE rank < 3


Resultado:

Espero que estes exemplos possam te ajudar a entender como as Window Functions funcionam e como elas podem te ajudar.

Referência:http://postgresguide.com/tips/window.html

GROUP_CONCAT no Postgres

O MySQL tem uma função muito legal chamada GROUP_CONCAT. Ela concatena os valores de uma coluna em uma string separados por um separador que por padrão é uma vírgula(,). Para exemplificar considere a seguinte estrutura no banco de dados(pode ser usada tanto no MySQL quanto no Postgres):

create table countries_languages(
country_code varchar(4) not null,
language varchar(32) not null,
primary key(country_code, language)
);

insert into countries_languages(country_code, language) values('BR', 'Portuguese');
insert into countries_languages(country_code, language) values('THA', 'Chinese');
insert into countries_languages(country_code, language) values('THA', 'Khmer');
insert into countries_languages(country_code, language) values('THA', 'Kuy');
insert into countries_languages(country_code, language) values('THA', 'Lao');


Ao executarmos a seguinte consulta:

select country_code, language as languages
from countries_languages
where country_code = 'THA'


Obteremos a seguinte saída:

THA	Chinese
THA	Khmer
THA	Kuy
THA	Lao

Usando o GROUP_CONCAT nesta outra consulta:

select country_code, GROUP_CONCAT(language) as languages
from countries_languages
where country_code = 'THA'
GROUP BY country_code


A saída será:

THA	Chinese,Khmer,Kuy,Lao

Certo! Bem legal e útil quando quisermos exportar dados ou apresentá-los, mas como podemos fazer isso no banco de dados Postgres?

No Postgres não existe a função GROUP_CONCAT mas nele temos arrays. Uma forma de ter a mesma saída que o MySQL usando arrays em Postgres é:

select country_code, array_to_string(array(SELECT language FROM countries_languages WHERE country_code = 'THA'), ',')
from countries_languages
where country_code = 'THA'
GROUP BY country_code


O resultado da sub-consulta "SELECT language FROM countries_languages WHERE country_code = 'THA'" é convertido para um array que por sua vez é convertido para uma string pela função array_to_string com cada valor do array separado por uma vírgula. É mais verboso e exige uma sub-query mas o resultado é o mesmo.

Referência:http://stackoverflow.com/a/4469002

Truques SQL

Inserindo a partir de uma consulta SQL

Supondo que temos a seguinte estrutura de tabelas:

CREATE TABLE users(

id int not null,

name text not null,

email text

);

CREATE TABLE user_searches_results(

id int not null,

id_user int not null,

search_date date

);

Agora como nós podemos guardar todos os id's da tabela users na tabela user_searches_results retornados na seguinte consulta: SELECT id FROM users WHERE name LIKE 'Maria%' ?

Este seguinte comando sql é capaz de fazer a tarefa de forma muito eficiente:

INSERT INTO user_searches_results(id, id_user, search_date)

SELECT 1, id, now() FROM users WHERE name LIKE 'Maria%';

Simples não?

Retornando um registro aleatório a partir de uma consulta

MySQL

SELECT column1, column2, ... FROM table ORDER BY rand() LIMIT 1

PostgreSQL

SELECT column1, column2, ... FROM table ORDER BY random() LIMIT 1

SQL Server

SELECT TOP 1 column1, column2, ... FROM table

ORDER BY NEWID()

DB2

SELECT column1, column2, RAND() as IDX 

FROM table 

ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Oracle

SELECT column1 FROM

( SELECT column1 FROM table

ORDER BY dbms_random.value )

WHERE rownum = 1

Selecionar o segundo maior(ou menor valor) de uma coluna

SELECT MAX(column)

  FROM table

 WHERE column < (SELECT MAX(column) FROM table )

Use MIN se você quiser o segundo menor valor e não se esqueça do operador >.

Copiar a estrutura de uma tabela sem copiar os dados(MySQL, PostgresSQL)

CREATE TABLE users_2 AS (SELECT * FROM users WHERE 1=0);

Observe que índices e constraints não são duplicados.

Gerar SQL para atualizar os dados de uma tabela baseado no conteúdo de outra tabela(PostgresSQL)

Considerem a seguinte estrutura e dados:

CREATE TABLE departments(

id int not null,

name text not null,

employee_count int

);

INSERT INTO departments values(1, 'Development', 0);

INSERT INTO departments values(2, 'Sales', 0);

INSERT INTO departments values(3, 'Marketing', 0);

CREATE TABLE employees(

id int not null,

id_department int not null,

name text not null

);

INSERT INTO employees VALUES(1, 2, 'Ryu');

INSERT INTO employees VALUES(2, 2, 'Ken');

INSERT INTO employees VALUES(3, 3, 'Chun-Li');

INSERT INTO employees VALUES(4, 1, 'Guile');

Certo. Agora precisamos atualizar a tabela departments com o número de empregados que cada uma contém. Com o seguinte sql os comandos de update serão gerados automaticamente:

SELECT 'UPDATE departments SET employee_count = ' || COUNT(e.id) 

  || ' WHERE id = ' || e.id_department || ';'

FROM employees e

GROUP BY e.id_department;

A linguagem SQL pode ser mais poderosa e útil do você imagina.

Locale e como isso pode afetar o retorno da função iconv

Execute o trecho de código abaixo na linha de comando o observe o resultado:

ini_set('display_errors', 1);
error_reporting(E_ALL);

header('Content-Type: text/plain; charset=utf-8');
$utf8_word = 'GOIÂNIA';
$ascii_word = iconv('UTF-8', 'US-ASCII//TRANSLIT', $utf8_word);
echo $ascii_word, "\n";
echo md5($ascii_word);
echo "\n";


No meu sistema(PHP 5.3.6-13ubuntu3.10 with Suhosin-Patch (cli)) a saída foi:

GOIANIA
1d92cfdaed61eba2c8dea6e670df9f38

Certo, agora executando o mesmo script através do browser e do servidor web a saída é a seguinte:

GOI?NIA
848aa917b7d132a581c0d9f91bc1b03f

Humm... Por que o mesmo script tem saídas diferentes quando executado na linha de comando e quando executado no servidor web?

Mas o que este script faz mesmo? Ele simplesmente usa a função iconv para converter uma string codificada com UTF-8(GOIÂNIA) para codificação US-ASCII, o nosso velho conhecido ASCII, usando transliteração o que faz com que quando um caractere não possa ser representado na codificação alvo a função tenta usar um caractere da codificação alvo que seja parecido. Mas ainda não sabemos poque o script tem saídas diferentes em ambientes diferentes.

Acontece que a função iconv é afetada pelo locale do ambiente, usando a função setlocale podemos verificar qual locale está sendo usado:

$current_locale = setlocale(LC_ALL, "0");
echo "Current locale:$current_locale", "\n";


No servidor web a resposta é "C" na linha de comando é "LC_CTYPE=pt_BR.UTF-8;LC_NUMERIC=C;LC_TIME=C;LC_COLLATE=C;LC_MONETARY=C;LC_MESSAGES=C;LC_PAPER=C;LC_NAME=C;LC_ADDRESS=C;LC_TELEPHONE=C;LC_MEASUREMENT=C;LC_IDENTIFICATION=C", que é o locale configurado no meu sistema operacional. Ou seja enquando no servidor web o locale é C na linha de comando é utf-8. Então é só ajustarmos o locale antes de chamar a função iconv para que ela tenha o mesmo comportamento em qualquer ambiente.

ini_set('display_errors', 1);
error_reporting(E_ALL);

setlocale(LC_ALL, 'pt_BR.UTF-8');
header('Content-Type: text/plain; charset=utf-8');
$utf8_word = 'GOIÂNIA';
$ascii_word = iconv('UTF-8', 'US-ASCII//TRANSLIT', $utf8_word);
echo $ascii_word, "\n";
echo md5($ascii_word);
echo "\n";


Mas existem algumas considerações a serem feitas, o locale configurado através da função setlocale deve estar instalado no servidor onde o código será executado, caso contrário a função setlocale simplesmente retorna FALSE e o resultado pode ser diferente para cada ambiente quando demonstrado no início do texto. Outra consideração é que o efeito da função setlocale é "por processo". Na documentação da função existe um "warning"  explicando que quando existirem vários scripts rodando em diferentes threads no servidor(algo muito comum se seu servidor tem várias requisições ao mesmo tempo) uma chamada a setlocale para mudar o locale vai afetar todos os scripts em execução no momento mesmo que o script em si não tenha chamado setlocale.

Espero que este texto possa ajudar as pessoas pegas de surpresa pelos resultados da função iconv. Até a próxima.

Referências:
http://stackoverflow.com/questions/9771886/what-factors-influence-a-successful-iconv-translit-conversion

phpsh um REPL para PHP

Vocês sabem o que a sigla REPL significa? Ela significa read-eval-print-loop. Ela é usada para denominar um abiente shell de programação interativa. É como um programa de linha de comando onde você pudesse executar os comandos de sua linguagem de programação favorita e observar o que cada comando faz imediatamente depois de apertar Enter. Diversas linguagens de programação possuem seus REPL's como Python, Ruby, Perl, Scala, Groovy, Forth, Lisp, Javascript etc. Aliás é bem provável que você que está lendo este texto agora já tenha um ambiente REPL para Javascript instalado e funcional, afinal, os browsers mais usados como Firefox, Chrome e Iternet Explorer possuem interpretadores Javascript. Aperte F12 no Chrome e Internet Explorer ou Ctrl + Shift + K no firefox e procure pela aba console.

O PHP também já vem com um REPL instalado. Se você executar o seguinte comando:
php -a
Verá que ele irá abrir o "Interative Shell". Mas infelizmente ele é muito limitado, no meu caso por exemplo aparece um til(~) cada vez que aperto a tecla Delete e o autoloading não está disponível. E no windows eu diria que ele simplesmente não funciona.

O pessoal do Facebook criou um REPL bem legal para PHP. Chamdo phpsh ele é implementado na maior parte em Python(pecado mortal!). Mas enfim, ele tem recursos legais como autoloading, opção para carregar código já existente e a tecla Delete funciona!

O endereço com instruções para instalá-lo está aqui. Para baixá-lo eu simplesmente clonei o repositório usando o git, mas você também pode obtê-lo através de um arquivo zip na mesma página.

Existem alguns módulos PHP que precisam estar instalados, são eles: pcre, posix e tokenizer. No caso do Python também existem algumas dependências. No meu caso tive que instalar o easy_install para instalar o pacote readline. Para instalar o easy_install tive que instalar o pacote python-setuptools. Depois de instalar o setuptools basta usar o comando para instalar o readline:
sudo easy_install readline
E depois segui as instruções para instalar globalmente:
cd phpsh (precisamos entrar no diretório raiz do phpsh depois de baixá-lo)

python setup.py build
sudo python setup.py install
phpsh

E pronto! Você já pode escrever código PHP e apertar enter para ver o resultado. Eu particularmente acho muito úteis estes ambientes REPL. Quando eu quero ver o que uma função faz ou simplesmente testar algumas poucas linhas de código é muito fácil abrir o terminal e já começar a escrever o código e poucos momentos depois já ver o resultado. Espero que isto ajude vocês tanto quanto me ajuda.

Estimando o valor de pi usando um quadrado, um círculo e probabilidade

Existe uma técnica bem legal de se estimar o valor da constante π usando o quadrado e círculo abaixo:

O valor do raio do círculo é 1. Assim a área do círculo é π * r ^ 2 = π * (1 * 1) = π.
E a área do quadrado é :2 ^ 2 = 2 * 2 = 4. Só lembrando que estou usando o símbolo ^ para representar a exponenciação.

E agora nós selecionamos milhares de pontos dentro do quadrado. Para uma boa aproximação devemos fazer isso milhões de vezes. Uma vez isso feito você pode usar a seguinte fórmula para encontrar o valor de π:
π/4 = número de pontos dentro do círculo / número total de pontos

modificando a fórmula para encontrar o valor de π:
π = (número de pontos dentro do círculo * 4) / número total de pontos

Considerando-se que para obtermos um bom resultado devemos escolher milhões de pontos, seria muito demorado e entediante fazer este processo manualmente, portanto, nada melhor do que usarmos um computador para isso. E o programa em PHP abaixo faz exatamente isso:



Acredito que não seja difícil entender o código. Neste exemplo selecionamos 5 milhões de pontos dentro do quadrado. A inspiração veio deste link, lá você também pode encontrar uma boa explicação de como a técnica funciona. Ou você pode usar a constante M_PI da linguagem PHP.

Eficiência Matemática

Vou mostrar-lhes dois trechos de código que fazem exatamente a mesma coisa:






Analisem os trechos de código e tentem descobrir o que eles fazem. Uma dica: a essência de tudo está na função calc.


Já analisou? Entendeu o que está acontecendo? Bom, estes trechos de código calculam 1 milhão de vezes a soma do intervalo de números entre 1 e um número aleatório entre 1 e 500. Ao mesmo tempo ele toma nota de quanto tempo este processo leva usando a função microtime.


Você conseguiu descobrir o que estava acontecendo antes de eu ter explicado? Se você não conseguiu, não tem problema, eu vou explicar. Comparando os dois trechos de código, qual você diria que apresenta com mais clareza o comportamento que expliquei anteriormente?


O primeiro trecho usa funções cujos nomes dão uma certa ideia sobre o que está acontecendo. "array_sum" retorna a soma dos valores dentro de um array. "range" retorna um array preenchido com números em um intervalo que vai do primeiro parâmetro passado até o segundo parâmetro passado. Então array_sum soma o conteúdo de um array, range retorna um array de números, bingo! Tudo o que o loop faz é somar os números naturais de 1 até um intervalo superior que varia de 1 até 500. Um milhão de vezes!


E o segundo trecho? Não há chamadas de funções nesta versão da função calc. Ela mais parece uma fórmula matemática. E realmente é, é a seguinte fórmula:

E ela simplesmente retorna a soma entre os números naturais de 1 até n.

Certo! O que eu quero demonstrar com tudo isso? Primeiro eu convido vocês a executar os trechos de código em seus computadores e comparar o tempo gasto nos cálculos tanto pelo trecho usando array_sum quanto pelo outro usando a fórmula matemática, no meu computador um core i7 2600 3,40 GHz os tempos em segundos, cada trecho foi executado três vezes, foram os seguintes:

array_sum:

• 42,979010105133
• 42,527684926987
• 43,008407831192

fórmula matemática:

• 9,8508331775665
• 9,889456987381
• 9,9301979541779

Não é um benchmark rigoroso ou bem feito, mas como vocês podem ver a diferença de tempo entre as duas abordagens rodando no meu computador é enorme. E acredito que aqueles que testarem o código também observarão grandes diferenças de tempo de execução dos dois trechos.

Eu acredito que a grande diferença de eficiência de execução se deve principalmente a dois pontos principais. A função range cria um array dinamicamente e isso envolve alocação de memória por parte do interpretador PHP, o que toma tempo. Mas é o segundo ponto que deve realmente impactar na performance, a função array_sum deve percorrer cada elemento do array gerado pela função range e laços de repetição afetam a performance significativamente.

Eu não tenho dúvidas de que o código usando array_sum é mais fácil de se ler, mais elegante e usa todo o poder do dinamismo da linguagem PHP. Mas a eficiência da abordagem matemática é inegável, e alguns até poderiam dizer que ela é tão elegante quanto a outra, sempre podemos adicionar um comentário informando o que a fórmula faz, não é? Acho que preciso voltar a estudar matemática.