Existem duas tabelas C e B.
Eles estão conectados por C.id=B.cid.
Eu preciso encontrar todos os C.ides onde:
cidtemis_true='Y'Eu tentei várias consultas, mas não consigo entender nenhuma delas. Esta é uma das minhas ideias.
select * from
C inner join B
on C.id=B.cid
where
-- loop all rows of B using B.cid
-- output if the row has is_true!='Y'
Digamos que você tenha as seguintes tabelas para uma plataforma de várias lojas
CREATE TABLE orders (
id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
store_id BIGINT NOT NULL,
ordered_at TIMESTAMPTZ NOT NULL
);
CREATE INDEX ON orders (store_id);
CREATE TABLE order_lines (
id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
order_id BIGINT NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
item_id BIGINT NOT NULL REFERENCES items (id),
quantity INT
);
CREATE INDEX ON order_lines (order_id);
CREATE INDEX ON order_lines (item_id);
CREATE TABLE items (
id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
store_id BIGINT NOT NULL,
color VARCHAR(255) NOT NULL,
size VARCHAR(255) NOT NULL,
category VARCHAR(255)
);
CREATE TABLE returns (
id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
order_line_id BIGINT NOT NULL REFERENCES order_lines (id) ON DELETE CASCADE,
returned_at TIMESTAMPTZ NOT NULL,
quantity INT NOT NULL,
reason VARCHAR(255)
);
CREATE INDEX ON returns (order_line_id);
A partir disso, gostaria de obter uma lista de todos os pedidos durante um período e calcular certas métricas, como quantos itens foram comprados, quantos foram devolvidos, etc. Também gostaria de fazer isso para um subconjunto de itens com base na cor ou tamanho, mas também quero que sejam "classificados". Por exemplo, eu gostaria de mostrar essas métricas para os itens que tiveram uma cor que fez parte das cores mais retornadas, no geral.
O que descobri até agora é fazer isso em duas consultas. A primeira é passar por todas as devoluções e agrupá-las por cor e somar os itens, devolver, conforme abaixo:
select
i.color,
trunc(sum(r.quantity)::numeric / sum(ol.quantity)::numeric, 2) as return_rate
from orders o
inner join
order_lines ol on ol.order_id = o.id
inner join
items i on i.id = ol.item_id
left outer join
returns r on r.order_line_id = ol.id
group by i.color
order by return_rate desc nulls last
limit 4;
color | return_rate
--------------+-------------
Black | 0.43
Blue | 0.41
White | 0.40
Yellow | 0.39
Com base nessa consulta, eu faria uma nova que agruparia todos os pedidos por dia (data) e, em seguida, somaria a taxa de retorno total e a taxa de retorno das principais cores retornadas durante um intervalo de tempo. Eu também gostaria de poder filtrar isso por exemplo, tamanho, categoria, etc. nos itens. Ele será usado para um relatório dinâmico onde as pessoas podem ver o número total de pedidos, devoluções, taxa, bem como um gráfico de linhas da taxa média de devolução, principais cores retornadas em um intervalo de tempo selecionado.
Existe alguma maneira melhor de fazer isso? Parece um pouco errado percorrer e fazer todas as junções duas vezes. Leia um pouco sobre as funções da janela, mas não consegui descobrir se isso era aplicável aqui.
Comecei a mexer aqui com minha abordagem ingênua e também com CTE para tentar reutilizar os dados em massa. https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/10700
Obviamente, o desempenho é um pouco diferente localmente, com cerca de 100 mil linhas. Aqui está um plano de análise explicativo da consulta CTE sendo executada localmente.
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=50730.74..50809.91 rows=2262 width=68) (actual time=350.787..351.912 rows=722 loops=1)
Group Key: ((order_return_items.date)::date), order_return_items.color
CTE order_return_items
-> HashAggregate (cost=33884.69..39240.19 rows=226239 width=33) (actual time=211.390..258.041 rows=194309 loops=1)
Group Key: o.ordered_at, i.color
Planned Partitions: 4 Batches: 5 Memory Usage: 9265kB Disk Usage: 7736kB
-> Hash Left Join (cost=5740.52..14725.07 rows=226239 width=25) (actual time=44.080..158.610 rows=226902 loops=1)
Hash Cond: (ol.id = r.order_line_id)
-> Hash Join (cost=3754.69..9321.56 rows=226239 width=29) (actual time=31.592..107.385 rows=226239 loops=1)
Hash Cond: (ol.item_id = i.id)
-> Hash Join (cost=3572.53..8544.81 rows=226239 width=28) (actual time=29.966..79.287 rows=226239 loops=1)
Hash Cond: (ol.order_id = o.id)
-> Seq Scan on order_lines ol (cost=0.00..4378.39 rows=226239 width=28) (actual time=0.012..10.556 rows=226239 loops=1)
-> Hash (cost=2363.90..2363.90 rows=96690 width=16) (actual time=29.931..29.931 rows=96690 loops=1)
Buckets: 131072 Batches: 1 Memory Usage: 5557kB
-> Seq Scan on orders o (cost=0.00..2363.90 rows=96690 width=16) (actual time=0.005..13.289 rows=96690 loops=1)
-> Hash (cost=137.63..137.63 rows=3563 width=17) (actual time=1.620..1.621 rows=4462 loops=1)
Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 300kB
-> Seq Scan on items i (cost=0.00..137.63 rows=3563 width=17) (actual time=0.014..0.754 rows=4462 loops=1)
-> Hash (cost=1248.70..1248.70 rows=58970 width=12) (actual time=12.385..12.385 rows=61098 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 3376kB
-> Seq Scan on returns r (cost=0.00..1248.70 rows=58970 width=12) (actual time=0.007..5.392 rows=61098 loops=1)
-> Sort (cost=11490.55..11496.20 rows=2262 width=52) (actual time=350.765..351.008 rows=6567 loops=1)
Sort Key: ((order_return_items.date)::date), order_return_items.color
Sort Method: quicksort Memory: 626kB
-> Hash Join (cost=6227.62..11364.52 rows=2262 width=52) (actual time=323.014..349.528 rows=6567 loops=1)
Hash Cond: (order_return_items.color = most_returned_colors.color)
-> CTE Scan on order_return_items (cost=0.00..4524.78 rows=226239 width=56) (actual time=211.393..223.913 rows=194309 loops=1)
-> Hash (cost=6227.60..6227.60 rows=2 width=32) (actual time=111.569..111.571 rows=2 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Subquery Scan on most_returned_colors (cost=6227.57..6227.60 rows=2 width=32) (actual time=111.564..111.566 rows=2 loops=1)
-> Limit (cost=6227.57..6227.58 rows=2 width=64) (actual time=111.564..111.565 rows=2 loops=1)
-> Sort (cost=6227.57..6228.07 rows=200 width=64) (actual time=111.562..111.563 rows=2 loops=1)
Sort Key: (trunc((sum(order_return_items_1.r_q) / sum(order_return_items_1.ol_q)), 3)) DESC NULLS LAST
Sort Method: top-N heapsort Memory: 25kB
-> HashAggregate (cost=6221.57..6225.57 rows=200 width=64) (actual time=111.538..111.550 rows=44 loops=1)
Group Key: order_return_items_1.color
Batches: 1 Memory Usage: 48kB
-> CTE Scan on order_return_items order_return_items_1 (cost=0.00..4524.78 rows=226239 width=48) (actual time=0.000..85.098 rows=194309 loops=1)
Também apreciaríamos feedback sobre a estrutura geral, bem como os índices, se houver!
No PostgreSQL, como você soma subelementos de um campo JSON?
Dado:Customer <--- Order
onde Ordertem um campo JSON quantities_per_product que contém a quantidade e a quantidade enviada para cada produto do Order.
Gostaria de obter um resumo Customerdas quantidades de cada produto para todos os pedidos do cliente.
Exemplo:
Customer 1
> Order 1
quantities_per_product
{
"product_A": {"qty": 10, "shipped": 5}
"product_B": {"qty": 20, "shipped": 2}
}
> Order 2
quantities_per_product
{
"product_A": {"qty": 15, "shipped": 8}
"product_C": {"qty": 7, "shipped": 1}
}
Resultado esperado para o Cliente 1:
quantities_per_product_summary
{
"product_A": {"qty": 25, "shipped": 13}
"product_B": {"qty": 20, "shipped": 2}
"product_C": {"qty": 7, "shipped": 1}
}
Estou tentando entender o funcionamento do SSL ao tentar me conectar a um servidor MySQL (via mysql-connector-python). Tenho MySQL (v5.7) e tenho 2 usuários. Um (root) é o usuário padrão e o outro usuário (ssl_user) tem a opção REQUIRE SSL habilitada. Se eu tentar me conectar ao ssl_user com a seguinte configuração, recebo um erro dizendo que ssl_user não tem acesso.
mysql_conf = {
'host': '127.0.0.1',
'port': '3306',
'user': 'root',
'password': '',
'ssl_disabled': True #Default value is False
}
conn = mysql.connector.connect(**mysql_conf)
Consigo me conectar ao MySQL se alterar o valor como 'ssl_disabled': False para o usuário específico.
Estou me conectando ao servidor mysql por meio do meu aplicativo flask. O que exatamente acontece ao me conectar por meio deste usuário criado com Require SSL? isso significa que a conexão deste usuário está criptografada? em caso afirmativo, onde acontece a criptografia?
Não consigo entender qual é exatamente a diferença entre conexão via 'ssl_disabled': False e conexão fornecendo certificados SSL?
converti meu banco de dados de acesso ao sql server ... agora quero replicá-lo e o banco de dados está apresentando erro de chave primária ... escrevi o código abaixo para adicionar uma nova coluna em todo o banco de dados, mas não consigo converter o ' coluna autoid' como chave primária...por favor, ajude a resolvê-lo.
USE Aidherpnew
GO
IF OBJECT_ID(N'tempdb..#TempSysTableNames') IS NOT NULL
BEGIN
DROP TABLE #TempSysTableNames
END;
DECLARE @ColumnName VARCHAR(250) = 'autoid'
,@DataType VARCHAR(250) = 'INT'
,@DefaultValue VARCHAR(250) = '1'
,@SQL VARCHAR(8000)
,@MaxRowNum INT
,@I INT = 1;
SELECT '[' + DB_NAME() + '].[' + OBJECT_SCHEMA_NAME([object_id],DB_ID()) + '].[' + name + ']' AS [name]
,ROW_NUMBER() OVER (ORDER BY [create_date]) AS RowNum
INTO #TempSysTableNames
FROM sys.tables
WHERE [type] = 'U';
SET @MaxRowNum = (SELECT MAX(RowNum)
FROM #TempSysTableNames);
WHILE (@I <= @MaxRowNum)
BEGIN
SET @SQL = (SELECT 'ALTER TABLE ' + [name] + ' ADD ' + @ColumnName + ' ' + @DataType + ' NOT NULL DEFAULT ' + @DefaultValue + ';'
FROM #TempSysTableNames
WHERE RowNum = @I);
PRINT(@SQL);
EXECUTE(@SQL);
SET @I += 1;
END;
Há algo que eu possa fazer para melhorar o desempenho dessa consulta? Incluí o plano de execução e a saída do Server Profiler. Existe um índice que pode ajudar? Vejo que há uma busca de índice que está demorando algum tempo. Posso fazer alguma coisa para ajudar nisso? no perfil do servidor vejo que as leituras parecem altas, pode haver algo para ajudar nisso?
SELECT dbo.Templates.VersionKey
FROM dbo.Templates INNER JOIN
dbo.Indications ON dbo.Templates.Id = dbo.Indications.TemplateId INNER JOIN
dbo.Sentences ON dbo.Indications.SentenceId = dbo.Sentences.Id INNER JOIN
dbo.SentenceParts ON dbo.Sentences.Id = dbo.SentenceParts.SentenceId INNER JOIN
dbo.SentencePartValues ON dbo.SentenceParts.Id = dbo.SentencePartValues.SentencePartId
WHERE (dbo.Templates.Status <> 8) AND
(dbo.Templates.IsCurrentVersion = 1) AND
(dbo.Indications.IsActive = 1) AND
(dbo.Indications.IsCurrentVersion = 1)
AND (NOT (Sentences.[IsDeleted] = 1))
AND (NOT (SentenceParts.[IsDeleted] = 1))
AND (NOT (SentencePartValues.[IsDeleted] = 1))
and
(SUBSTRING(dbo.SentencePartValues.LiteralValue, 12, 36) = 'a2278339-8add-ff3d-8baa-9654b8b8d00b')
plano de execução https://www.brentozar.com/pastetheplan/?id=By68aKtfp
Perfilador do SQL Server
Restaurei (COMPLETO) um banco de dados usando SSMS para um novo servidor. Quando fiz isso, recebemos erros indicando que a conta não conseguiu fazer login, provavelmente devido a um SID incorreto, pois confirmamos que a conta foi transferida durante a restauração e tinha as permissões apropriadas. Tentamos excluir e adicionar novamente o usuário através da GUI do SSMS e recebemos os mesmos erros de login de nosso aplicativo (o usuário não existe). Para corrigir isso, a única solução que consigo encontrar é especificar a criação do login com um SID específico, como mostra a consulta abaixo. Quando executo isso, o SQL Server parece ter um problema com o "" no domínio. Não consigo encontrar outra maneira de especificar isso que funcione. Estou faltando sintaxe? Como fazer login em uma conta de serviço AD com um SID especificado se o sintático "" não for aceito?
CREATE LOGIN [AD_DOMAIN\login]
WITH PASSWORD = 'password_here', SID = SID_HERE;
SELECT * FROM sys.sql_logins WHERE name = 'AD_DOMAIN\login';
GO
Neste ponto seria mais fácil apenas criar uma nova conta de serviço no AD, mas perguntando aqui para ver se estou perdendo alguma coisa estúpida....
Baseado em documentos Sqlite: https://www.sqlite.org/datatype3.html#type_conversions_prior_to_comparison , especialmente esta declaração:
Se um operando tiver afinidade INTEGER, REAL ou NUMERIC e o outro operando tiver TEXT ou BLOB ou nenhuma afinidade então a afinidade NUMERIC será aplicada ao outro operando.
Eu esperaria a seguinte consulta:
CREATE TABLE `invoice` (
`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
`amount` DOUBLE PRECISION DEFAULT NULL
);
insert into `invoice` (`amount`) values (4.0);
insert into `invoice` (`amount`) values (15.0);
insert into `invoice` (`amount`) values (4.0);
select *,
typeof(amount), amount = '4',
typeof(sum(amount)), sum(amount) = '4', sum(amount) = '4.0', sum(amount) = 4
from invoice
group by id;
para retornar o mesmo resultado para sum(amount) = '4'cada amount = '4'linha, pois ambos os tipos de operando têm o mesmo tipo em cada comparação (verificado usando typeof(), para não, SUM()a comparação está funcionando conforme o esperado).
Demonstração: http://sqlfiddle.com/#!5/59238/2
Tenho 2 modelos relacionados, model_A e model_B
Há uma relação 1:n entre eles
Significa que um registro no model_A pode ter vários registros relacionados no model_B
Gostaria que model_A tivesse uma coluna gerada (e armazenada)
Essa coluna gerada deve ser booleana
Deve ser definido como verdadeiro se houver 1 ou mais registros relacionados no modelo_B
Deve ser falso se houver 0 registros relacionados no modelo_B
Como escrevo a expressão para gerar esses valores booleanos?
Estive lendo a documentação da Microsoft sobre backups de log no SQL Server e me deparei com a seguinte declaração:
(Log Backup) Especifica um backup somente do log de transações. O backup do log é feito desde o último backup de log executado com êxito até o final atual do log. Antes de poder criar o primeiro backup de log, você deve criar um backup completo."
A partir disso, entendo que um backup de log faz backup dos registros do último backup de log. No entanto, também sei que os backups diferenciais e completos também incluem alguns registros de log. Isso me leva à minha pergunta:
Os backups de log também incluem os registros de log dos quais foi feito backup nos backups completos ou diferenciais anteriores? Ou um backup de log faz backup estritamente dos registros de log a partir do último backup de log, com backups completos ou diferenciais não intervindo ou afetando o que é copiado no backup de log subsequente?
Qualquer introspecção seria muito bem recebida. Desde já, obrigado!