Perguntas[sql](coding)

Tenho tabelas com milhões (possivelmente bilhões) de linhas, então realmente preciso ser eficiente com a consulta.

Nesta consulta, estou unindo várias tabelas. O segmento em questão é:

LEFT JOIN 
    dbo.GCSOCTPS dbo_GCSOCTPS ON (GC_TBMED.MED_CLASS_NUM = dbo_GCSOCTPS.CLASS_NUM) 
                              AND (GC_TBMED.MED_SOC_NUM = dbo_GCSOCTPS.SOC_NUM)
                              AND (GC_TBMED.MED_EFF_DATE = dbo_GCSOCTPS.EFF_DATE)
                              AND (GC_TBMED.MED_CANC_DATE = dbo_GCSOCTPS.CANC_DATE)) 

GC_TBMEDtem datas no DATEformato, dbo_GCSOCTPStem datas no DATETIMEformato. Infelizmente, devido à forma como nossa empresa usa dados, não posso alterar isso.

Qual seria a maneira mais eficiente de comparar essas colunas? CAST? CONVERT? Já vi pessoas convertendo para o formato de texto e comparando dessa forma. Espero que alguém possa me orientar com base em sua experiência com conjuntos de dados muito grandes.

Se eu executar este bloco de código, obtenho um valor para HDHPQ:

SELECT TOP 200 
    HDHPQ,
    SOC_NUM, 
    EFF_DATE,
    CLASS_NUM,
    CANC_DATE
FROM
    dbo.GCSOCTPS
WHERE
    SOC_NUM = '25521'
    AND CLASS_NUM = '37'
    AND CANC_DATE IS NULL;

Isto é retornado:

HDHPQ    SOC_NUM    EFF_DATE                  CLASS_NUM    CANC_DATE 
N         25521    2025-01-01 00:00:00.000      37         NULL

Se eu executar esse bloco de código, também receberei dados retornados:

SELECT TOP 200 
    MED_SOC_NUM,
    MED_EFF_DATE,
    MED_CLASS_NUM,
    MED_CANC_DATE
FROM
    [dbo].[AS_tblTBMED] GC_TBMED
WHERE 
    GC_TBMED.MED_SOC_NUM = '25521'
    AND GC_TBMED.MED_CLASS_NUM = '37'
    AND GC_TBMED.MED_CANC_DATE IS NULL;

Isso é retornado:

MED_SOC_NUM    MED_EFF_DATE    MED_CLASS_NUM    MED_CANC_DATE
25521        2025-01-01        37                NULL

Cada um deles retorna uma linha. Preciso juntá-los para obter todos os dados da segunda consulta e o valor de HDHPQ da primeira consulta.

Então eu executo esta consulta:

SELECT DISTINCT TOP 200 
    dbo_GCSOCTPS.HDHPQ,
    dbo_GCSOCTPS.SOC_NUM, 
    dbo_GCSOCTPS.EFF_DATE,
    dbo_GCSOCTPS.CLASS_NUM,
    dbo_GCSOCTPS.CANC_DATE,
    GC_TBMED.MED_SOC_NUM,
    GC_TBMED.MED_EFF_DATE,
    GC_TBMED.MED_CLASS_NUM,
    GC_TBMED.MED_CANC_DATE
FROM
    [dbo].[AS_tblTBMED] GC_TBMED
LEFT JOIN 
    dbo.GCSOCTPS dbo_GCSOCTPS ON (GC_TBMED.MED_CLASS_NUM = dbo_GCSOCTPS.CLASS_NUM) 
                              AND (GC_TBMED.MED_SOC_NUM = dbo_GCSOCTPS.SOC_NUM)
                              AND (GC_TBMED.MED_EFF_DATE = CAST(dbo_GCSOCTPS.EFF_DATE as DATE))
                              AND (GC_TBMED.MED_CANC_DATE = CAST(dbo_GCSOCTPS.CANC_DATE as DATE)) 
WHERE
    GC_TBMED.MED_SOC_NUM = '25521'
    AND GC_TBMED.MED_CLASS_NUM = '37'
    AND GC_TBMED.MED_CANC_DATE IS NULL
    AND dbo_GCSOCTPS.EFF_DATE >= '2025-01-01';

E um conjunto de registros vazio é retornado. Se eu comentar as duas datas na junção, obtenho dados. Portanto, presumo que os campos de data não estejam sendo equalizados corretamente, já que são iguais e, portanto, eu deveria obter dados se eles fossem incluídos na consulta.

Tenho duas tabelas PostgreSQL: objectse metadata. Cada objeto tem uma sizepropriedade que representa seu tamanho em bytes e pode ser armazenado em um local exclusivo com storage_id. Nos metadados, a total_sizepropriedade de todos os objetos de cada armazenamento com um dado storage_idé mantida. Tabelas simplificadas:

CREATE TABLE IF NOT EXISTS objects (
  object_id UUID PRIMARY KEY,
  storage_id UUID NOT NULL,
  size BIGINT NOT NULL,
  FOREIGN KEY (storage_id) REFERENCES metadata(storage_id)
);

CREATE TABLE IF NOT EXISTS metadata (
  storage_id UUID PRIMARY KEY,
  total_size BIGINT DEFAULT 0
);

Para manter o total_sizena metadatatabela, tenho gatilhos que sempre que um objeto é inserido ou excluído, ele total_sizeé atualizado, ou seja, aqui está o gatilho para inserção:

CREATE OR REPLACE FUNCTION update_size_on_insert() RETURNS TRIGGER AS $$
BEGIN
  UPDATE metadata
  SET total_size = total_size + NEW.size
  WHERE storage_id = NEW.storage_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trg_update_size_on_insert
AFTER INSERT ON objects
FOR EACH ROW
EXECUTE FUNCTION update_size_on_insert();

O problema é que inserções/exclusões simultâneas podem sobrescrever o comando, o total_sizeque leva a dados inválidos. Como posso alterar o gatilho para que ele contenha um bloqueio em nível de linhaSET total_size = total_size + NEW.size quando o comando for executado? Eu estava pensando na FOR UPDATEinstrução, mas isso requer um comando SELECT. Posso usar PERFORM ... FOR UPDATE;para bloquear a linha?

Estou executando uma consulta para verificar se algum registro contém um espaço em branco DateOfBirth, mas os dados retornados não são o que eu esperava.

Eu executei o seguinte:

SELECT TOP 5 DateOfBirth, *
FROM [MyDataBase].[dbo].[CustomerRecord] 
WHERE DateOfBirth = ''

Quando executo isso, meus resultados mostram linhas como esta:

O Information_SCHEMA.Columnsmostra que a coluna é definida como datee COLUMN_DEFAULTé NULLe IS_NULLABLEestá definido como YES.

Então por que ele não retorna linhas em branco?

Eu tenho uma matriz como esta:

["f383224c-aa95-40c9-a4e4-fa41747f49c5","c783234c-aa95-40c9-a4e4-fa41747f49c6"]

E eu quero selecionar com qualquer, mas recebi um erro não é possível converter text[] para uuid

SELECT c.name FROM cars c 

LEFT JOIN location_orders_cars loc ON loc.car_id = c.id 

WHERE loc.user_id = ANY($1::uuid) AND loc.location_orders_id = $2 AND loc.is_back = NULL 

LIMIT 10;

$1 = matriz de UUID acima

$ 2 = 355

o que estou fazendo errado aqui?

Tenho uma tabela com alterações de status que quero dividir em ilhas e, eventualmente, obter minha ilha mais atual com base em quando meu status mudou. Estou usando esta consulta para recuperar os códigos de status atual, anterior e próximo da minha tabela:

select 
    *, 
    case 
        when ping.GpsStatusTypeId <> ping.previousStatus 
            then 1 
            else 0 
    end as islandStarted
from
    (select 
         Id,
         GpsStatusTypeId,
         CreatedAt,
         lag(GpsStatusTypeId, 1) over (order by CreatedAt) as previousStatus,
         lead(GpsStatusTypeId, 1) over (order by CreatedAt) as nextStatus
     from 
         dbo.GpsPing p) ping

O resultado é assim:

Quero retirar apenas minha última ilha ( GpsStatusTypeId = 21) do resultado.

Temos um banco de dados Access que usamos para enviar amostras de teste. Temos 18 métodos diferentes que podemos executar, e cada método normalmente tem um número diferente de execuções de teste associadas a ele (embora isso dependa do remetente). Sendo assim, nosso banco de dados atual permite apenas uma entrada de método por registro, para que o método possa ser facilmente associado às execuções necessárias; o problema com essa abordagem é que qualquer amostra pode exigir qualquer quantidade de métodos, o que, por sua vez, requer mais entradas de registro (com nossa configuração atual).

Tenho pesquisado uma maneira de fazer com que haja múltiplas seleções de métodos por registro. Sei que isso resultaria em dados "desnormalizados", mas é para facilitar a vida do remetente/solicitante. Quero então usar uma consulta ou outro meio para "normalizar" os dados para as equipes de teste (ou seja, separar os registros por método selecionado).

A mesa de apoio que eu criei se parece um pouco com isso (é mais larga):

Gostaria que ficasse assim:

O código SQL para esta consulta é:

SELECT [Sample SubmissionSingleBatch].AutoID, 
[Sample SubmissionSingleBatch].[Lab Batch ID], 
[Sample SubmissionSingleBatch].[IRIS Batch ID], 
[Sample SubmissionSingleBatch].Requester, 
[Sample SubmissionSingleBatch].Samples, 
GetRunsWanted([Method].[Value],[CFPPLinearRuns],[CFPPRuns],
[CFPP12Runs],[CloudMiniRuns],[CloudRuns],[Conf_CFPPRuns],
[Conf_CloudMiniRuns],[Conf_CloudRuns],[Conf_HFRRRuns],
[Conf_PourMiniRuns],[Conf_PourAutoRuns],[DistRuns],[HFRRRuns],
[PourMiniRuns],[PourAutoRuns],[Pour_MANRuns],[RancimatRuns],
[SFPPRuns]) AS Runs, 
[Sample SubmissionSingleBatch].Method.Value AS Method,
 GetPriorityWanted([Method].[Value],[CFPPLinearPriority],
[CFPPPriority],[CFPP12Priority],[CloudMiniPriority],[CloudPriority],
[Conf_CFPPPriority],[Conf_CloudMiniPriority],[Conf_CloudPriority],
[Conf_HFRRPriority],[Conf_PourMiniPriority],[Conf_PourAutoPriority],
[DistPriority],[HFRRPriority],[PourMiniPriority],[PourAutoPriority],
[Pour_MANPriority],[RancimatPriority],[SFPPPriority]) AS Priority, 
[Sample SubmissionSingleBatch].[Instrument Specific], 
[Sample SubmissionSingleBatch].[Drop-Off date], 
[Sample SubmissionSingleBatch].Comments, 
[Sample SubmissionSingleBatch].[Samples in Class 1 FC?], 
[Sample SubmissionSingleBatch].[Flash Point of sample °C],
 GetReceivedWanted([Method].[Value],[CFPPLinearReceived],
[CFPPReceived],[CFPP12Received],[CloudMiniReceived],[CloudReceived],
[Conf_CFPPReceived],[Conf_CloudMiniReceived],[Conf_CloudReceived],
[Conf_HFRRReceived],[Conf_PourMiniReceived],[Conf_PourAutoReceived],
[DistReceived],[HFRRReceived],[PourMiniReceived],[PourAutoReceived],
[Pour_MANReceived],[RancimatReceived],[SFPPReceived]) AS Received, 
GetStartedWanted([Method].[Value],[CFPPLinearStarted],[CFPPStarted],
[CFPP12Started],[CloudMiniStarted],[CloudStarted],[Conf_CFPPStarted],
[Conf_CloudMiniStarted],[Conf_CloudStarted],[Conf_HFRRStarted],
[Conf_PourMiniStarted],[Conf_PourAutoStarted],[DistStarted],
[HFRRStarted],[PourMiniStarted],[PourAutoStarted],[Pour_MANStarted],
[RancimatStarted],[SFPPStarted]) AS Started,
 GetFinishedWanted([Method].[Value],[CFPPLinearFinished],
[CFPPFinished],[CFPP12Finished],[CloudMiniFinished],[CloudFinished],
[Conf_CFPPFinished],[Conf_CloudMiniFinished],[Conf_CloudFinished],
[Conf_HFRRFinished],[Conf_PourMiniFinished],[Conf_PourAutoFinished],
[DistFinished],[HFRRFinished],[PourMiniFinished],[PourAutoFinished],
[Pour_MANFinished],[RancimatFinished],[SFPPFinished]) AS Finished, 
[Sample SubmissionSingleBatch].[Retain?]
FROM 
[Sample SubmissionSingleBatch];

Esta segunda imagem é exatamente o que eu quero. O problema é que se trata de uma consulta na qual tenho uma função VBA personalizada em execução (código fornecido abaixo) e, nesta saída, os valores das execuções não podem ser editados. Eu precisaria que eles fossem editáveis ​​caso ocorresse um erro de entrada, um valor de execução precisasse ser alterado, etc.

Entendo que posso simplesmente editar os dados na tabela inicial, mas tenho certeza de que é óbvio que a segunda imagem tem uma aparência muito mais amigável.

Existe uma maneira de obter o que preciso ou estou levando o Access além de sua capacidade/limites?

Código da função:

Function GetRunsWanted(MethodValue As String, CFPPLinearRuns As Variant, 
     CFPPRuns As Variant, CFPP12Runs As Variant, CloudMiniRuns As Variant, 
     CloudRuns As Variant, Conf_CFPPRuns As Variant, 
     Conf_CloudMiniRuns As Variant, Conf_CloudRuns As Variant, 
     Conf_HFRRRuns As Variant, Conf_PourMiniRuns As Variant,
     Conf_PourAutoRuns As Variant, DistRuns As Variant, 
     HFRRRuns As Variant, PourMiniRuns As Variant, 
     PourAutoRuns As Variant, Pour_MANRuns As Variant, 
     RancimatRuns As Variant, SFPPRuns As Variant) As Variant
    Select Case MethodValue
        Case "CFPP LINEAR/EN16329"
            GetRunsWanted = CFPPLinearRuns
        Case "CFPP/LQP055"
            GetRunsWanted = CFPPRuns
        Case "CFPP12"
            GetRunsWanted = CFPP12Runs
        Case "CLOUD MINI/ASTM D7689"
            GetRunsWanted = CloudMiniRuns
        Case "CLOUD/ASTM D5771"
            GetRunsWanted = CloudRuns
        Case "Conf_CFPP/LQP055"
            GetRunsWanted = Conf_CFPPRuns
        Case "Conf_CLOUD MINI/ASTM D7689"
            GetRunsWanted = Conf_CloudMiniRuns
        Case "Conf_CLOUD/ASTM D5771"
            GetRunsWanted = Conf_CloudRuns
        Case "Conf_HFRR/ISO12156"
            GetRunsWanted = Conf_HFRRRuns
        Case "Conf_POUR MPP/ASTM D7346"
            GetRunsWanted = Conf_PourMiniRuns
        Case "Conf_POUR_AUTO/ASTM D5950"
            GetRunsWanted = Conf_PourAutoRuns
        Case "DIST_D86"
            GetRunsWanted = DistRuns
        Case "HFRR/ISO12156"
            GetRunsWanted = HFRRRuns
        Case "POUR MPP/ASTM D7346"
            GetRunsWanted = PourMiniRuns
        Case "POUR_AUTO/ASTM D5950"
            GetRunsWanted = PourAutoRuns
        Case "POUR_MAN/ASTM D97"
            GetRunsWanted = Pour_MANRuns
        Case "RANCIMAT/LQP092"
            GetRunsWanted = RancimatRuns
        Case "SFPP"
            GetRunsWanted = SFPPRuns
        Case Else
            GetRunsWanted = Null
    End Select
End Function

Olá a todos, estou com um problema específico. Estou usando o Oracle DB

Para o exemplo, usarei o banco de dados Northwind.

Quero unir as tabelas: Clientes , Pedidos , Detalhes do pedido e Produtos .

Solução simples:

SELECT Customers.customerid, Customers.companyname, 
       Orders.orderid, Orders.orderdate, 
       Products.productname, Products.unitprice
FROM Customers
INNER JOIN Orders
  ON Customers.customerid= Orders.customerid
INNER JOIN Orderdetails 
  ON Orders.orderid = Orderdetails.orderid
INNER JOIN Products 
  ON Orderdetails.productid = Products.productid

Agora, quero limitar a saída aos 3 pedidos mais recentes por cliente, mostrando apenas o produto mais caro do pedido. A saída ficaria mais ou menos assim (Edição: a tabela não está sendo exibida corretamente):

Tenho uma solução usando LATERAL()e uma subseleção, mas, especialmente em big data, quero usar isso, LATERAL()tem muitos custos e torna o processo bastante lento. Então, quero ter uma solução SEMLATERAL()

Outra solução que tenho tentado é usar, ROW_NUMBER()que é muito mais rápida:

Select * FROM( 
  SELECT Customers.customerid, Customers.companyname, 
         Orders.orderid, Orders.orderdate, 
         Products.productname, Products.unitprice,
         ROW_NUMBER() OVER (PARTITION BY Customers.customerid
                            ORDER BY o.orderdate desc, p.unitprice desc ) AS rn
        ...
 ) WHERE rn <= 3;

Agora, se o limite fosse 1, não haveria problema. Mas não consigo encontrar uma solução aceitável para um limite >= 2 para os pedidos, como expliquei acima. Tentei uma segunda solução ROW_NUMBER() OVER (PARTIONION BY Orders.orderid ...), mas para que isso funcionasse, precisaria de algum aninhamento. O problema com o aninhamento é que estou prestes a criar algo como um analisador sintático para uma aplicação. E aninhamento em excesso pode se tornar muito complicado e confuso (especialmente para fins de manutenção).

Então eu estava pensando em uma junção, existe uma solução SQL pura no Oracle para:

• Junte 3 ou mais mesas.
• Use um Limite >= 2 em uma tabela (B) no meio da Junção que tem várias junções com outra tabela a seguir.
• Isso não usa LATERAL()
• Não é necessário aninhamento?

Editar/Adicionado: A maneira que tenho usado LATERAL():

Como este é um banco de dados de exemplo, tive que reestruturar o LATERAL que estava usando. (Para o exemplo do Northwind, tenho que unir produtos em detalhes do pedido em pedidos, no meu banco de dados não preciso da etapa extra com uma tabela entre eles)

SELECT C.customerid, C.companyname, 
       O.orderid, O.orderdate, 
       P.productname, P.unitprice
FROM Customers C
INNER JOIN LATERAL( 
  SELECT Orders.orderid, Orders.orderdate FROM Orders 
  WHERE Orders.customerid = C.customerid 
  ORDER BY orderdate DESC FETCH FIRST 3 ROWS ONLY ) O 
  ON 1=1 
INNER JOIN LATERAL( 
  SELECT Products.unitprice, Products.productname FROM Orderdetails 
  INNER JOIN Products ON Products.productid = Orderdetails.productid 
  WHERE Orderdetails.orderid = O.orderid 
  ORDER BY Products.unitprice DESC FETCH FIRST 1 ROWS ONLY) P on 1=1

Usei ON 1=1porque a instrução on é obrigatória para o Oracle em uma instrução Join, mas já estou unindo dentro do LATERAL().

Tenho refatorado usando o índice espacial em Haversine,

Eu queria escrever uma instrução SQL que verificasse postagens em um raio de 10 km com o valor de ponto (0,90) para obter informações sem distância, mas confirmei que o índice não é usado se 'ou' for usado.

Então, tentei combinar as duas consultas SELECT usando UNION ALL , mas o desempenho permaneceu tão lento quanto antes, tornando-o ineficaz. Testei isso com 50.000 linhas de dados e não houve melhora perceptível — mesmo com o índice espacial sendo usado corretamente neste caso.

Como último recurso, pensei em separar as postagens sem informações de distância das postagens com informações de distância para separar a API, mas a interface mudou e agora o front não consegue refatorar, então quero excluí-la o máximo possível. Obrigado 🙏

select b1_0.id,
   b1_0.title,
   b1_0.item_time,
   b1_0.item_price,
   b1_0.created_date,
   b1_0.chat_count,
   b1_0.scrap_count,
   st_distance_sphere(b1_0.location, ST_GeomFromText('POINT(127.0 37.5)', 4326)),
   b1_0.address,
   b1_0.board_state,
   (select i1_0.stored_file_name
    from image i1_0
    where i1_0.board_id = b1_0.id
    order by i1_0.id LIMIT 1)
from board b1_0
where b1_0.board_type = 'SELL'
  and (ST_Contains(ST_Buffer(ST_GeomFromText('POINT(127.0 37.5)', 4326), 0.1), b1_0.location)
           OR (latitude = 90.0 AND longitude = 0.0))
order by b1_0.created_date desc
limit 0, 8;

Tenho uma tabela na qual um novo registro é adicionado aproximadamente a cada segundo. O ID está faltando na tabela. A coluna "Hora" mostra o tempo em milissegundos, que é zerado ao final do dia.

A coluna "Data" contém uma data no formato dia-mês-ano. O que devo escrever em uma consulta SQL para obter o último registro adicionado a cada 1-2 segundos?

Eu tenho essa mesa

cada registro representa uma "mudança de estado" da máquina específica DM139 onde

• MachineCode = código da máquina (nvarchar)
• DoCreation = registro de data e hora de quando a máquina mudou seu estado (data e hora)
• MachineStateCode = novo estado da máquina, onde 99 = reinicializando, 0 = inicializando, 3 = erro, 1 = iniciando, 2 = funcionando e assim por diante (int)

Preciso criar uma nova coluna, vamos chamá-la de MachineStateCodeAdjusted, onde seu valor é igual a 3 se o MachineStateCode anterior for 3 e o MachineStateCode atual não for 2, caso contrário, deve ter o valor atual do MachineStateCode. (a próxima tabela é o que preciso)

Usei a função LAG para obter o MachineStateCode anterior (e funciona), mas para funcionar como eu quero, preciso obter o MachineStateCodeAdjusted anterior, que não é acessível para a função LAG.

A consulta SQL que estou executando é esta:

   SELECT MachineCode
       , DoCreation
   , MachineStateCode
   , IIF(LAG(MachineStateCode, 1, MachineStateCode) OVER (ORDER BY DoCreation ASC) = 3 
  AND MachineStateCode<>2, 3, MachineStateCode)  AS MachineStateCodeAdjusted       
  FROM mch.MachineStateChanges

e o resultado que obtive me mostra claramente que não funciona como eu quero

Também tentei com um CTE recursivo e funciona, mas é muito lento! A extração da consulta leva 4 minutos e 2 dias de trabalho, enquanto eu preciso extrair 1 ano!

Qual é a melhor (e rápida) abordagem para consertar isso?