Faheem Mitha's questions

Estou usando o SQLite 3.27.2-3 no Debian Buster/stable. Eu poderia usar algum outro banco de dados, mas para o meu caso de uso (LuaTeX), algo com baixa sobrecarga parece fazer sentido.

Meu caso de uso é que tenho uma letra (no sentido de correspondência) que se refere a outras letras no texto. Eu tenho uma tabela que é construída automaticamente a partir de referências no texto.

Digamos que a tabela seja assim

RowID           From     To
=====           ====     ==
2019.06.05       PC      SW
2019.06.11       PC      SB
2019.06.12
2019.06.15       SB      PC
2019.06.22       DO      SW

e que corresponde a algumas linhas de uma tabela maior, que chamarei de attachments.

E vamos supor que há uma tabela de abreviaturas que se parece com

Abbreviation     Name
============     ====
B                Bashful
D                Dopey
DO               Doc
H                Happy
G                Grumpy
PC               Prince Charming
SB               Sleeping Beauty
S                Sleepy
SN               Sneezy

Então eu quero uma tabela ( abbreviations) que tenha uma lista das abreviações que ocorrem na minha pequena lista de correspondências. Isso se pareceria

Abbreviation     Name
============     ====
DO               Doc
PC               Prince Charming
SB               Sleeping Beauty
SW               Snow White

Algo como a consulta abaixo funciona, aproximadamente. Mas também recebo uma saída em branco para quando não corresponde. Estou recebendo erros do SQLite (ou LuaSQL, para ser preciso), então estou querendo saber se é por causa da saída em branco.

Abordagens melhores (e mais simples) apreciadas.

SELECT abbreviations.Abbreviation,
       abbreviations.Name
FROM attachments
LEFT OUTER JOIN abbreviations ON attachments.`From`=abbreviations.Abbreviation
WHERE attachments.RowID IN ('2019.06.05',
                            '2019.06.11',
                            '2019.06.12',
                            '2019.06.15',
                            '2019.06.22')
UNION
SELECT abbreviations.Abbreviation,
       abbreviations.Name
FROM attachments
LEFT OUTER JOIN abbreviations ON attachments.`To`=abbreviations.Abbreviation
WHERE attachments.RowID IN ('2019.06.05',
                            '2019.06.11',
                            '2019.06.12',
                            '2019.06.15',
                            '2019.06.22');

Isso já foi solicitado no Stack Overflow , mas apenas para o MySQL. Estou usando o PostgreSQL. Infelizmente (e surpreendentemente) o PostgreSQL não parece ter algo como CHECKSUM table.

Uma solução PostgreSQL seria boa, mas uma genérica seria melhor. Encontrei http://www.besttechtools.com/articles/article/sql-query-to-check-two-tables-have-identical-data , mas não entendo a lógica usada.

Background: Eu reescrevi algum código gerador de banco de dados, então preciso verificar se o código antigo e o novo produzem resultados idênticos.

Essa é essencialmente a mesma pergunta de Como renomear uma coluna em uma tabela de banco de dados usando SQL? .

Então, quero renomear uma coluna em uma tabela. Essa coluna faz parte de um conjunto de tabelas que reside dentro de um esquema PostgreSQL, que está em um banco de dados PostgreSQL.

No entanto, também tenho restrições como chaves estrangeiras envolvendo esta coluna. Não está claro para mim se este comando também cuidará das restrições. Se não, como devo lidar com isso?

Como alternativa, também tenho esse banco de dados disponível como um arquivo de despejo. Faria sentido editar o arquivo de despejo e, em caso afirmativo, como?

É possível ter uma restrição exclusiva personalizada da seguinte maneira? Suponha que eu tenha duas cols subsete type, ambas as strings (embora os tipos de dados provavelmente não importem).

Se typefor 'true', então eu quero que a combinação de typee subsetseja única. Caso contrário, não há restrição. Estou usando o PostgreSQL 8.4 no Debian.

Estou usando o PostgreSQL 8.4, mas gostaria de uma solução SQL padrão, se possível. Considere a tabela a seguir.

corrmodel=# SELECT * from data limit 1;
   id    | datasubgroup_id | datafile_id |           sequence           | index | seqindex | margstat | pvalue 
---------+-----------------+-------------+------------------------------+-------+----------+----------+--------
 1033473 |               3 |          10 | GGTGACCCCAAGCTCAGGGCTGACCTGC | 19042 |          |  70.7634 |      0

Desejo retornar a consulta que possui as seguintes propriedades.

1. Todas as linhas com o mesmo datafile_ide indexsão agrupadas.
2. Os grupos são classificados primeiro por média pvaluedescendente, depois por average
   margstat, onde as médias estão em cada grupo.

As duas tabelas nas quais estou fazendo esta consulta têm 2,2 milhões e 3,1 milhões de linhas, então gostaria de algo razoavelmente eficiente. Cada grupo consiste em 5 linhas. Esta solução de @Lamak funciona, mas tive alguns problemas para entender e acho que uma solução usando funções de janela pode ser algo que eu realmente possa entender. O seguinte está próximo, mas não correto, pois o grupo não é preservado neste caso.

SELECT datafile_id, 
       index, 
       pvalue, 
       margstat, 
       Avg(pvalue) 
         OVER ( 
           partition BY datafile_id, index) AS avg_pval, 
       Avg(margstat) 
         OVER ( 
           partition BY datafile_id, index) AS avg_margstat 
FROM   data 
ORDER  BY avg_pval DESC, 
          avg_margstat; 

Aqui estão as primeiras 10 linhas do resultado da consulta para um dos meus conjuntos de dados. Eu gostaria de algo assim, mas correto.

datafile_id | index | pvalue | margstat | avg_pval | avg_margstat 
-------------+-------+--------+----------+----------+--------------
          30 |   781 |      1 |  13.1568 |    0.998 |     12.52546
          30 |   781 |      1 |  12.3585 |    0.998 |     12.52546
          30 |   781 |      1 |  12.3495 |    0.998 |     12.52546
          30 |   781 |   0.99 |  11.9554 |    0.998 |     12.52546
          30 |   781 |      1 |  12.8071 |    0.998 |     12.52546
          23 |  1428 |   0.99 |  12.1711 |    0.998 |      12.6777
          23 |  1428 |      1 |  12.6451 |    0.998 |      12.6777
          23 |  1428 |      1 |  12.8814 |    0.998 |      12.6777
          23 |  1428 |      1 |  12.8969 |    0.998 |      12.6777
          23 |  1428 |      1 |   12.794 |    0.998 |      12.6777

Estou usando a seguinte consulta SQL duas vezes com valores diferentes de limit. No meu caso, 40 e 100, embora isso realmente não importe. Minha pergunta é: posso combinar essas duas consultas em uma consulta? Provavelmente seria mais rápido. sampleé uma matriz PG.

Não sei quantas informações adicionais são necessárias, mas se precisar de mais detalhes, pergunte. Estou relutante em adicionar detalhes sobre o esquema, a menos que seja necessário, pois isso tornará a pergunta muito mais longa.

SELECT   SUM(motif)
FROM     (SELECT    ROW_NUMBER() OVER (PARTITION BY crossvalnum ORDER BY crossvalnum, pvalue DESC, margstat) AS r,
                    (seqindex IS NOT NULL)::INTEGER                                                        AS motif,
                    crossval.crossvalnum,
                    data.margstat,
                    data.pvalue
         FROM       data
                    INNER JOIN datasubgroup
                    ON         data.datasubgroup_id=datasubgroup.id
                    INNER JOIN crossval
                    ON         datasubgroup.crossval_id=crossval.id
         WHERE      data.seqindex                      =ANY(crossval.sample)
         OR         data.seqindex                  IS NULL
         )
         AS q
WHERE    q.r <= limit
GROUP BY crossvalnum;

O valor de 40 é

sum 
-----
  25
  22
  19
  16
  24
(5 rows)

O valor de 100 é

sum 
-----
  32
  28
  24
  23
  31
(5 rows)

Update: motifé definido como uma variável indicadora, (seqindex IS NOT NULL)::INTEGER. O SUMcorresponde a GROUP BY crossvalnumcomo uma consulta agregada. Portanto, a soma ocorre em cada grupo crossvalnum. Portanto, há uma função de janela dentro de uma consulta agregada. Eu quero que a saída seja duas colunas.