Início / dba / Perguntas / 197244
Accepted
Daniel
Asked: 2018-02-08 01:17:36 +0800 CST

Precisa de ajuda com a consulta em três tabelas relacionadas

  • 772

Estou projetando um banco de dados para uma editora para rastrear o envio de revistas e também para manter um histórico dele.

Eu tenho três tabelas (simplificadas):

insira a descrição da imagem aqui

Subscriber
  id (pk)
  subscribers_number (unique)
  first_name
  last_name
  shipping_address
  last_payment

Shipping
  id (pk)
  date
  issue

ShippingAddress
  id (pk)
  shipping_id (fk)
  subscriber_id (fk)
  subscribers_number
  first_name
  last_name
  shipping_address

NonDeliveryReport
  id (pk)
  shipping_address_id (fk)
  reason
  resolved

Subscriberé uma lista de pessoas que pagam para receber uma edição mensal de sua revista. Nem todo assinante é elegível para receber a próxima edição. Para simplificar, um assinante recebe a próxima edição apenas se last_paymenta data estiver nos últimos 60 dias. Há mais algumas condições, mas falarei sobre isso mais tarde.

Quando a próxima edição estiver pronta para ser enviada, um Shippingserá criado.

Em seguida, selecionamos todos os Subscribers elegíveis e criamos um ShippingAddresspara cada um e copiamos o valor de subscribers_number, e first_namede para o novo . Fazemos isso para que tenhamos um histórico de envios imutável, mesmo que mude de endereço ou seja excluído do banco de dados. Para facilitar o uso, há uma chave estrangeira anulável além da cópia impressa do arquivo .last_nameshipping_addressSubscriberShippingAddressSubscribersubscriber_idsubscribers_number

Se a empresa de correio não puder entregar a um assinante ShippingAddress, eles retornarão um relatório de não entrega para nós. Para cada relatório, um NonDeliveryReporté criado e faz referência ao arquivo ShippingAddress.

Agora estamos chegando à parte importante da minha pergunta:

Quando criamos um Frete, temos que selecionar todos os Subscribers, que são elegíveis. Elegível é todo assinante, exceto qualquer um, que tenha um relatório de não entrega anexado a ele, que NÃO seja resolved. (Para simplificar, simplesmente ignoramos todas as outras condições.)

Então eu preciso de duas consultas:

  1. Selecione todas as assinaturas qualificadas.
  2. Selecione todos os assinantes que não são elegíveis.

A consulta nº 2 foi gerenciável e bastante fácil:

SELECT DISTINCT
  Subscriber.id
FROM Subscriber
  INNER JOIN ShippingAddress ON (Subscriber.id = ShippingAddress.subscriber_id)
  INNER JOIN NonDeliveryReport ON (ShippingAddress.id = NonDeliveryReport.shipping_address_id)
WHERE NonDeliveryReport.resolved IS NULL;

Mas as consultas que escrevi para o caso nº 1 sempre retornaram menos ou mais resultados do que o esperado. Estou preso nisso desde a semana passada e simplesmente não consigo fazê-lo funcionar. Espero que você possa me ajudar com isso.

Isso não faz parte da minha pergunta principal, mas quaisquer pensamentos (em forma de comentários, por favor) sobre o design do meu banco de dados são bem-vindos. Eu já estava pensando em adicionar um campo deliverable (bool)e Subscriberapenas preencher o valor programaticamente, ao importar os NDRs, mas estou hesitante porque isso provavelmente anularia o propósito da normalização do banco de dados.

Edit # 1: O fechamento que cheguei foi a seguinte consulta, mas ela retorna muitas linhas. *suspirar*

SELECT DISTINCT
  Subscriber.id
FROM Subscriber
  LEFT JOIN ShippingAddress ON (Subscriber.id = ShippingAddress.Subscriber_id)
  LEFT JOIN NonDeliveryReport ON (ShippingAddress.id = NonDeliveryReport.ShippingAddress_id)
WHERE NonDeliveryReport.korrigiert_am IS NOT NULL OR NonDeliveryReport.id IS NULL OR ShippingAddress.id IS NULL

Edit # 2: Depois de pensar ainda mais (eu não achava que isso fosse mais possível), encontrei uma solução.

Quando não qualificados são assinantes que têm um NDR aberto (resolvido IS NULL), então elegível é qualquer pessoa que não tenha NDR além de qualquer pessoa que tenha mais de 0 NDR não resolvido.

-- List of ELIGIBLE Subscribers
SELECT DISTINCT Subscriber.id
FROM Subscriber
LEFT JOIN ShippingAddress ON (Subscriber.id = ShippingAddress.subscriber_id)
WHERE ShippingAddress.id IS NULL OR Subscriber.id NOT IN
  (
    SELECT ShippingAddress.subscriber_id
    FROM ShippingAddress
    INNER JOIN NonDeliveryReport ON ShippingAddress.id = NonDeliveryReport.shipping_address_id
    WHERE (NonDeliveryReport.id IS NOT NULL AND NonDeliveryReport.resolved IS NULL)
    GROUP BY ShippingAddress.subscriber_id
  )
ORDER BY Subscriber.id;
database-design join

4 respostas

  1. Eu acredito que você está querendo algo um pouco assim;

    SELECT DISTINCT
Subscriber.id
FROM Subscriber
 INNER JOIN ShippingAddress ON (Subscriber.id = 
ShippingAddress.subscriber_id)
  LEFT OUTER JOIN NonDeliveryReport ON (ShippingAddress.id = NonDeliveryReport.shipping_address_id)
WHERE NonDeliveryReport.shipping_address_id IS NULL OR NonDeliveryReport.resolved IS NOT NULL;
    • 0

  2. Você não forneceu nenhum dado de teste em sua pergunta, portanto, isso não foi totalmente testado. Você também não especificou uma plataforma RDBMS (SQL Server, Oracle, etc.). Eu usei o SQL Server e algumas expressões de tabela comuns.

    Seu inelegível é qualquer assinante em que havia um endereço não entregue E qualquer assinante em que o last_payment seja há mais de 60 dias. Eu usei um UNION(o que eliminará quaisquer duplicatas). Os assinantes elegíveis são qualquer pessoa que NÃO seja elegível.

    ;

WITH SubscriberIneligible
AS (
    SELECT Subscriber.id        --non-deliverable
    FROM Subscriber
    INNER JOIN ShippingAddress ON (Subscriber.id = ShippingAddress.subscriber_id)
    INNER JOIN NonDeliveryReport ON (ShippingAddress.id = NonDeliveryReport.shipping_address_id)
    WHERE NonDeliveryReport.resolved IS NULL

    UNION

    SELECT Subscriber.id        --last payment over 60 days
    FROM Subscriber
    WHERE Last_Payment < DATEADD(DAY, - 60, SYSDATETIME())
    )
    ,SubscriberEligible
AS (
    SELECT Subscriber.id        --eligible subscribers
    FROM Subscriber s
    WHERE NOT EXISTS (
            SELECT *
            FROM SubscriberIneligible
            WHERE id = s.id
            )
    )
SELECT *
    ,'N' AS Eligible
FROM SubscriberIneligible

UNION

SELECT *
    ,'Y' AS Eligible
FROM SubscriberEligible
    • 0

  3. IMHO, seu design de banco de dados é OK.Hope tipo de dados também estão ok.

    A tabela de envio não é muito clara. Explique a finalidade da coluna

    Não diga "But query 1 kills me". diga o problema exato. quanto tempo leva. se está dando a saída correta ou não, etc.

    BTW com seu design existente, você pode evitar distintos dessa maneira

    SELECT 
  Subscriber.id
FROM dbo.Subscriber
INNER JOIN dbo.ShippingAddress ON (Subscriber.id = ShippingAddress.subscriber_id)
where exists(
select shipping_address_id from NonDeliveryReport NDR
WHERE ShippingAddress.subscriber_id= NDR.shipping_address_id AND
 NonDeliveryReport.resolved IS NULL
)

    Eu já estava pensando em adicionar uma entrega de campo (bool) ao Assinante e apenas preencher o valor programaticamente, ao importar os NDRs, mas estou hesitante porque isso provavelmente anularia o propósito da normalização do banco de dados.

    seu pensamento está correto. você deve adicionar isDeliverable (bool)na tabela Assinante. Isso não anula o propósito da Normalização. Primeiramente devemos criar o banco de dados Normalize tanto quanto possível. Agora de acordo com a necessidade e volume de dados e frequência temos liberdade de DeNormalization. É muito recomendado pelo livro RDMS.

    Performance is Main Goal

    Caso o desempenho da consulta esteja prejudicando, você pode adicionar algumas colunas para desnormalizar e, portanto, reduzir a condição JOIN.

    você pode até introduzir shipping_id (fk) , subscriber_id (fk) in NonDeliveryReport table, mas depende muito do seu requisito de MIS, volume de dados

    • 0
  4. Best Answer

    Esta é a solução certa.

    -- List of ELIGIBLE Subscribers
SELECT DISTINCT Subscriber.id
FROM Subscriber
LEFT JOIN ShippingAddress ON (Subscriber.id = ShippingAddress.subscriber_id)
WHERE ShippingAddress.id IS NULL OR Subscriber.id NOT IN
  (
    SELECT ShippingAddress.subscriber_id
    FROM ShippingAddress
    INNER JOIN NonDeliveryReport ON ShippingAddress.id = NonDeliveryReport.shipping_address_id
    WHERE (NonDeliveryReport.id IS NOT NULL AND NonDeliveryReport.resolved IS NULL)
    GROUP BY ShippingAddress.subscriber_id
  )
ORDER BY Subscriber.id;
    • 0

relate perguntas