Estou modelando tokens extraídos de tweets e tenho a estrutura da tabela abaixo:
CREATE TABLE process (
id SERIAL PRIMARY KEY,
...
);
CREATE TABLE tweet (
id SERIAL PRIMARY KEY,
process_id INTEGER NOT NULL REFERENCES process(id) ON DELETE CASCADE ON UPDATE CASCADE,
cleaned_text TEXT,
process_tweet_id INTEGER NOT NULL,
constraint tbl_statusid_processid unique(status_id, process_id)
...
);
CREATE TABLE token (
id SERIAL PRIMARY KEY,
tweet_id INTEGER NOT NULL REFERENCES tweet(id) ON DELETE CASCADE ON UPDATE CASCADE,
type TEXT,
value TEXT,
...
);
A tabela de tokens possui uma chave estrangeira para a tabela de tweets e a tabela de tweets possui uma chave estrangeira para a tabela de processos.
O campo 'type' na tabela de tokens pode ser 1 de 7 ou 8 valores possíveis (por exemplo, 'LOCATION', 'PERSON', etc.). Os tipos possíveis são estáticos e aplicados em outro lugar dentro do aplicativo, portanto, não há chance de qualquer desvio desses tipos definidos.
Estou tentando criar uma consulta que retornará todos os tweets referenciando um determinado process_id, o número total de tokens referenciando cada tweet, bem como o número de cada TIPO de token referenciando cada tweet (cada tipo tendo sua própria coluna no resultado definir).
id | status_id | tokencount | loctoken | persontoken
-------+--------------------+------------+----------+-------------
6158 | 672636476602122241 | 3 | 2 | 1
10295 | 674183390430552064 | 4 | 2 | 2
O conjunto de resultados acima é basicamente o que espero obter de volta. Assim, o tweet ID# 6158 tem um total de 3 tokens, 2 dos quais são tokens de localização e 1 é um token de pessoa. O ID do tweet nº 10295 tem um total de 4 tokens, 2 locais e 2 pessoas.
O que tenho até agora é um simples JOIN, mas não tenho certeza de como incluir a contagem de cada tipo de token por tweet...
SELECT tweet.id, tweet.status_id, count(token.id) AS tokenCount
FROM tweet
INNER JOIN token on token.tweet_id = tweet.id
WHERE tweet.process_id = 5
GROUP BY tweet.id;
Achei que poderia simplesmente adicionar count(token.type = 'LOCATION') como abaixo, mas isso simplesmente repetiu a coluna count(token.id).
SELECT tweet.id, tweet.status_id, count(token.id) AS tokenCount, count(token.type = 'LOCATION) AS locToken, count(token.type = 'PERSON') AS personToken
FROM tweet
INNER JOIN token on token.tweet_id = tweet.id
WHERE tweet.process_id = 5
GROUP BY tweet.id;
Alguém pode fornecer a consulta que retornaria o que estou procurando? Esperançosamente, com uma boa explicação da solução?
Também gostaria muito de saber porque count(token.type = 'LOCATION') não funcionou e simplesmente retornou o mesmo número que count(token.id). Eu tentei por um capricho e realmente pensei que iria receber um erro.
Você estava quase lá, você pode fazer isso com
case when ... end