Vérace's questions

Respondendo a esta pergunta ,

Dada esta tabela (construída a partir da pergunta):

CREATE TABLE wordcount (id SERIAL NOT NULL PRIMARY KEY, description TEXT NOT NULL);

INSERT INTO wordcount (description) VALUES ('What a great day');
INSERT INTO wordcount (description) VALUES ('This is a product. It is useful');

produza este resultado:

     id  | word_count | unique_word_count |  Description                        
---------+------------+-------------------+---------------
     1   |  4         | 4                 | What a great day
     2   |  7         | 6                 | This is a product. It is useful

Eu dei a resposta (correta), que você pode encontrar aqui .

No entanto, em um comentário, o OP fez outra pergunta - e se a string em questão fosse ['a', ' ', ' ', 'b']e minha solução quebrasse completamente - para começar, a string nem entraria INSERTna tabela.

Então, a questão agora é, como se lida com strings como esta - ou seja, com apóstrofos, colchetes etc. Vou dar minha própria resposta e também oferecer um bônus por uma solução mais elegante.

Soluções com múltiplas opções serão altamente valorizadas, assim como aquelas que mostrarem evidências de "pensar fora da caixa" (desculpe o clichê - mas cabe aqui! :-) ). Eu também vou dar uma explicação detalhada do meu raciocínio - que vai ganhar elogios também! As opções que mencionam outros servidores também ganharão mérito. Obviamente, só posso conceder o bônus a uma pessoa, mas votarei positivamente em todas as respostas decentes.

Só posso oferecer um bônus em dois dias - então postarei minha resposta e oferecerei o bônus (+100) quando puder. Além disso, quaisquer soluções que lidem com strings com as quais eu não possa lidar - ainda não foram exaustivamente testadas.

Como um seguimento a esta pergunta , eu tenho um de minha autoria.

A pergunta original diz respeito ao uso de uma CASEinstrução com > de 100 opções e a instrução deve ser usada em 4 lugares - então obviamente o SQL é bastante complicado. A pergunta do OP dizia respeito ao SQL Server 2012, minha pergunta, no entanto, é sobre o PostgreSQL.

Na minha resposta , propus o uso de a VIEWcomo uma solução "one-stop-shop" - ou seja, declare o VIEWuma vez, use-o em qualquer lugar - e isso se aplica a qualquer consulta no futuro também e a qualquer variante dela.

Outro pôster (@AndriyM) propôs o uso de a CROSS APPLYpara resolver o problema, que é outra solução. A sintaxe do PostgreSQL éJOIN LATERAL

Em seguida, adicionei um CTE ( Common Table Expression ) à minha resposta original como outra solução possível.

Então, o OP agora tem 5 opções:

1. CASE
2. VIEW
3. JOIN LATERAL( CROSS APPLYpara SQL Server)
4. CTE
5. Separate table

Excluí a opção de alterar os dados subjacentes, pois, frequentemente neste fórum, consultores/DBAs/programadores não têm permissão para alterar os dados subjacentes - torna as respostas mais interessantes também!

Obviamente, uma CASEexpressão com > 100 opções (x4) é terrivelmente complicada e complexa - mas quando é uma boa ideia usar CASEe em que ponto ela se torna menos em vez de mais?

Na minha opinião (e não apenas porque é a minha resposta!), a VIEWé a solução ideal - é simples, funcionará para todos os RDBMS e é permanente e funcionará para todas as consultas agora e no futuro, caso o OP deseje modificar a consulta .

A JOIN LATERALconstrução funcionará também como uma espécie de tabela derivada, que é praticamente o que a CTEtambém é. Ambos podem ser usados ​​na mesma linha na mesma consulta.

Qual das 5 abordagens é melhor/melhor e em que ponto a técnica (facilidade de uso, velocidade, otimização do plano de consulta) se inclina a favor da solução específica?

Esta pergunta é a versão PostgreSQL de uma pergunta sobre o MySQL 5.6 aqui . Originalmente, era uma pergunta para ambos os RDBMSs, mas me foi sugerido que, dadas as diferentes capacidades dos dois sistemas, eu deveria dividir a pergunta - em particular, acho que os CTEs (cláusula WITH) devem tornar a consulta muito mais elegante e legível!

Suponha que eu tenha uma lista de tumores (esses dados são simulados a partir de dados reais):

CREATE table illness (nature_of_illness VARCHAR(25), created_at DATETIME);

INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Lung',   '2018-01-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Cervix', '2018-02-03 17:50:32');
-- 2017, with 1 Cervix and Lung each for the month of Jan - tie!
INSERT INTO illness VALUES ('Cervix', '2017-01-03 15:45:40');
INSERT INTO illness VALUES ('Lung',   '2017-01-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Cervix', '2017-02-03 17:50:32');

Você quer descobrir qual tumor em particular foi mais comum em um determinado mês - até agora tudo bem!

Agora, você notará que para o mês 1 de 2017, há um empate - então não faz sentido escolher um aleatoriamente e dar isso como resposta - então os empates devem ser incluídos - isso torna o problema muito mais desafiador.

Eu tenho uma solução, mas é bastante complexa - gostaria de saber se minha solução é ótima ou não. O violino PostgreSQL está aqui ! A consulta no violino é muito complicada - vou dar uma olhada no uso de CTEs.

Minha primeira resposta (que funciona com PostgreSQL e MySQL) está incluída no violino, mas não a postarei aqui, pois acredito que será substituída pelos recursos superiores do PostgreSQL e seria apenas uma cópia da minha resposta para o MySQL pergunta!

Esta questão foi inspirada por esta [fechada] e é praticamente idêntica a esta , mas usando RDBMSs diferentes (PostgreSQL vs. MySQL).

Suponha que eu tenha uma lista de tumores (esses dados são simulados a partir de dados reais):

CREATE table illness (nature_of_illness VARCHAR(25), created_at DATETIME);

INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Lung',   '2018-01-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Cervix', '2018-02-03 17:50:32');
-- 2017, with 1 Cervix and Lung each for the month of Jan - tie!
INSERT INTO illness VALUES ('Cervix', '2017-01-03 15:45:40');
INSERT INTO illness VALUES ('Lung',   '2017-01-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Cervix', '2017-02-03 17:50:32');

Você quer descobrir qual tumor em particular foi mais comum em um determinado mês - até agora tudo bem!

Agora, você notará que para o mês 1 de 2017, há um empate - então não faz sentido escolher um aleatoriamente e dar isso como resposta - então os empates devem ser incluídos - isso torna o problema muito mais desafiador.

A resposta correta é:

  Year    Month  Tumour count      Type
  2017        1             1    Cervix  -- note tie
  2017        1             1      Lung  --   "   "
  2017        2             3      Lung
  2018        1             5    Cervix
  2018        2             3      Lung

Um bônus adicional seria ter o nome do mês aparecendo como texto em vez de um número inteiro.

Eu tenho uma solução, mas é bastante complexa - gostaria de saber se minha solução é ótima ou não. O violino MySQL está aqui !