TheDemonLord's questions

O banco de dados é MariaDB 10.3.25

Eu tenho 2 colunas que são relevantes:

UserID e LastUpdate

UserID consiste em usuário@domínio LastUpdate é um campo de data.

Aqui está o meu problema - eu tenho a consulta atual:

select a.UserID, 
substring_index(a.UserID, '@', -1), 
max(a.lastupdate) 
from MyTable a 
group by a.UserID
having max(a.lastupdate) < '2020-03-31' 

Isso mostra todos os usuários que não atualizaram em pouco mais de um ano e o domínio. No entanto, há o seguinte cenário que eu quero considerar:

UserID LastUpdate
[email protected] 2020-08-16
[email protected] 2019-05-16
[email protected] 2021-05-05

Com a consulta atual, [email protected] será capturado e, portanto, o domínio A.com será capturado como não em uso, mas o usuário [email protected] ainda está ativo - quero fazer um Max(a.lastupdate ), mas agrupe-o, substring_index(a.UserID, '@', -1) mas também cuspa todos os usuários desse domínio. Tenho certeza que a resposta está me encarando...

Isso está no MSSQL/Azure SQL.

Tenho a seguinte tabela de vendas:

Estou tentando obter uma saída que mostre para cada SKU, o número total de pedidos (muito fácil) E o número total de itens de linha para um pedido que contém esse SKU.

Então para a primeira parte:

select SKU, count(distinct(sales_order_id) as "Order_Count"  from Table T
group by SKU

Fácil o suficiente, no entanto, a segunda parte está fazendo minha cabeça -

A saída desejada ficaria assim:

Como obtenho uma contagem do total de Line_item_IDs para cada pedido que contém um SKU? Idealmente em uma única consulta sem uma carga de junções malucas ou subconsultas.

Eu tenho a seguinte tabela, em um banco de dados SQL do Azure que tem valores duplicados que estou tentando somar.

Aqui está a lógica: Se o PaymentID for único, então Sum Payment, se o creditID for único, então somar crédito, se o debitid for único, então somar débito. E pegue o max(source) A ideia é pegar uma única linha, por ID com valores distintos para o ID somado.

Por exemplo:

158464166 | -185,15 | 0 | -3,45 | CC | 5910551 | 5910548

(no acima - eu peguei o min(paymentid) para torná-lo mais bonito

Observe que, embora no snippet acima, Creditid e Debitid tenham apenas um ID duplicado, é possível que eles tenham IDs distintos, portanto, qualquer código terá que lidar com isso. PaymentID será sempre único.

Também é possível que os valores para pagamento, crédito e débito não sejam únicos (por exemplo, um pagamento de $ 50 é feito duas vezes para um único ID), então não podemos agrupar no pagamento.

cheguei até aqui:

SELECT id, sum(payment), sum(credit), sum(debit), max(source), creditid, debitid  
FROM (  
  SELECT *,  
         COUNT(*) OVER (PARTITION BY id) AS cnt  
  FROM Temp_Payment) AS t  
WHERE t.cnt > 1  

group by id, creditid, debitid

mas não está dando o resultado esperado.

Eu tenho um Banco de Dados SQL do Azure, uma Tabela que possui 3 Colunas Relevantes: Marca, ID e Mês/Ano e uma Tabela Temporária que simplesmente lista o Mês/Ano com um ID.

A tabela mostra uma lista de todos os IDs que estavam ativos naquele determinado mês.

Mês/Ano é uma coluna de carimbo de data, com a data como o primeiro do Mês. A coluna ID é apenas um ID.

O objetivo é ter uma saída que, para cada mês, mostre a contagem de todos os registros que estavam ativos neste mês, mas não ativos no mês seguinte (Churn) e IDs que não estavam ativos no mês anterior, mas estavam ativos neste mês (Novo) e ter estes agrupados por marca e mês assim:

Marca | Churn | Novo | Mês
1 | 10 | 12 | 01-12-2019
2 | 11 | 9 | 01-12-2019

Devo também observar que Table1 tem 19 milhões de linhas.

Tentei o código abaixo:

Declare @Id int
declare @my date

set @Id = 300
set @my = '2019-12-01'

select t1.[brand],
count(c.[ID]) as [churn],
count(n.[ID]) as [new],
t1.[month/year]
from Table1 T1

left join Table1 c on
c.[ID] = t1.[ID] and c.[ID] not in (select [ID] from Table1 where [Month/Year] = (select [month/year] from Temp_Date where id = (@Id +1)))
left join Table1 n on
n.[ID] = T1.[ID] and n.[ID] not in (select [ID] from Table1 where [Month/Year] = (select [month/year] from Temp_Date where id = (@Id -1)))

where t1.[Month/Year] = @my
group by t1.[Brand], t1.[Month/Year]

Que apesar de ter produzido uma saída, acho que não estava certo e demorou muito, provavelmente devido ao meu uso liberal de Sub-consultas em Joins.

Minha pergunta é dupla - alguém pode ajudar de uma maneira melhor para contar o número de linhas e juntá-las e existe uma maneira melhor de fazer isso sem tantas subconsultas?

Aqui está o meu problema, Source DB é Azure MSSQL - eu tenho uma tabela que lista todas as assinaturas vendidas. As colunas relevantes são:

Número da conta, grupo de assinatura, data de compra, data de cancelamento

Data de cancelamento contém valores NULL (para assinaturas que ainda não foram canceladas). Uma conta pode ter várias assinaturas, em vários grupos de assinaturas.

Os dados de exemplo são:

Número da conta | Grupo de assinatura | Data de compra | Data de cancelamento |

1 | A | 04/02/2013 | 04/02/2014 |
1 | A | 04/02/2013 | NULO |
1 | B | 04/02/2013 | NULO |
2 | C | 07/05/2018 | 08/05/2020 |
3 | C | 20/3/2020 | NULO |
3 | C | 08/04/2020 | NULO |

etc.

Estou procurando contar o número de grupos de assinatura para cada cliente e para cada mês - para que a saída seja algo assim:

Cliente | Contagem de A | Contagem de B | Contagem de C | Mês/Ano |
1 | 1 | 1 | 0 | 10/2020 |
3 | 0 | 0 | 2 | 10/2020 |
1 | 1 | 1 | 0 | 09/2020 |
3 | 0 | 0 | 2 | 09/2020 |
1 | 1 | 1 | 0 | 08/2020 |
2 | 0 | 0 | 1 | 08/2020 |
3 | 0 | 0 | 2 | 08/2020 |

Eu posso escrever a consulta para um determinado mês:

selecione t.[número da conta],
COUNT(CASE WHEN t.[Grupo de assinatura] = A então 1 ELSE NULL END) como "Contagem de A",
COUNT(CASE WHEN t.[Grupo de assinatura] = B então 1 ELSE NULL END) ) como "Contagem de B",
COUNT(CASE WHEN t.[Grupo de assinatura] = C então 1 ELSE NULL END) como "Contagem de C",
'09/2020' como 'Mês/Ano'
da tabela t
onde (t .[Data da compra] <= '30/09/2020' e (t.[Data de cancelamento] é nula ou t.[Data de cancelamento] > '10/01/2020'))
Agrupar por t.[Número da conta], [Mês ano]

No entanto, espero que alguém possa me ajudar a modificar o acima para que ele seja executado por todos os meses (com base no MIN da data de compra e no MAX na data do cancelamento)

O banco de dados é o Postgres.

Estou com um problema em que tenho uma tabela com as seguintes colunas: ID, Product_ID Customer_ID, Price e Item_Type.

Esta tabela é unida em Product_ID a outra tabela. Para um determinado ID de produto, há o preço padrão (onde o Customer_ID está em branco) para um determinado tipo de item (Novo, Renovação, Resgate etc.), também pode haver uma lista de preços específica do cliente (onde Customer_ID é unido à Tabela Clientes ).

O problema é que esse banco de dados tem sido mal mantido. Para Item_Type existem 2 Preços que para esta edição nos interessam: Renew e Renewal. Alguns preços têm um preço de renovação, alguns têm um preço de renovação e alguns têm ambos.

select p.id, pr.customer_id, p.period, pr.price, pr.item_type
from products p
right join prices pr on
pr.product_id = p.id and pr.period = p.period and pr.customer_id is null
where p.status = 'PURCHASED'

Como exemplo, isso produz o seguinte:

121751  8407508 12 29.95 RENEW
121751  8407508 12 35.95 RENEWAL

A causa é que este produto tem 2 preços diferentes para Renovação e Renovação - olhando para o código do aplicativo de front-end - se houver um item_type 'RENEWAL', esse preço será usado, caso contrário, o preço 'RENEW' será usado.

Eu tentei usar uma instrução case, tanto na cláusula select quanto na where:

case pr.item_type 
    when 'RENEWAL' then pr.price
    when 'RENEW' then pr.price
    end

Mas ainda recebo as duas linhas - não tenho certeza de como fazer uma pesquisa condicional e hierárquica, por exemplo, se a condição A for correspondida, retornar a linha que corresponde à condição A, se nenhuma correspondência for encontrada, a condição B for correspondida e retornar a linha que corresponde à condição B etc.

Eu preciso multiplicar valores de duas tabelas e somar os resultados. O banco de dados é Postgres ou MSSQL (longa história).

MesaBought_Products

ID | Name | Customer_ID | Product_ID | Foo | Bar | Blarg
1  | test | 123         | 321        | NULL| 1   | NULL
2  | tast | 123         | 231        | 5   | NULL| 20
3  | tost | 456         | 321        | NULL| 3   | NULL

MesaPrice

ID | Item | Product_ID | Cost
1  | Foo  | 321        | 10
2  | Bar  | 321        | 5
3  | BASE | 321        | 100
4  | BASE | 231        | 50
5  | Blarg| 231        | 0.5
6  | Foo  | 231        | 1

Eu preciso de um resultado assim:

Customer | Products | Revenue
123      | 321, 231 | 170

Para o Cliente 123 existem 2 produtos: 321 e 231, com preço base de 100 e 50 respectivamente; mais 1 x item 'Bar' em 5; mais 5 x itens 'Foo' em 1; e 20 x itens 'Blarg' a 0,5.

Isto é o que tenho para começar:

select sum(p.cost) from price p
join Bought_Products bp on bp.product_id = p.product_ID
where p.item = 'BASE'

Eu preciso adicionar os valores relevantes 'Foo', 'Bar' ou 'Blarg' Bought_Productsmultiplicados pelo respectivo Price.Cost(correspondendo ao cabeçalho da coluna ao item).

Ainda não usei funções matemáticas básicas em SQL. Não tenho certeza de como unir as 2 tabelas para depois multiplicar os valores. Com subconsultas e junções subsequentes?

Tenho 2 tabelas: Cliente e Faturamento, Cliente contém detalhes genéricos (nome, endereço, você entendeu) e Faturamento contém itens que são faturados.

A data final de cobrança do item é variável (algumas pessoas estão em um ciclo mensal, outras em 3 meses, 6 meses 1 ano etc.).

No Item de Cobrança o cliente pode ter diversos pacotes - alguns deles são pacotes que o marcam como Revendedor. Minha consulta SQL atual se parece com isso:

select customer.addresses_0_firstname AS AdminFName, 
       customer.addresses_0_lastname AS AdminLName, 
       customer.addresses_0_organisation AS CompanyName, 
       customer.addresses_0_address1 AS Address1, 
       customer.addresses_0_address2 AS Address2, 
       customer.addresses_0_city AS City, 
       customer.addresses_0_postcode AS Zip, 
       customer.addresses_0_country AS CountryID, 
       customer.addresses_0_email AS Email, 
       (CASE WHEN billing.lines_0_description like '%Package0%' 
             THEN 'False' 
             ELSE 'true' END) AS Reseller, 
       max(billing.end_date) 
from customer 
INNER JOIN billing on customer._id = billing.customer_$id 
where customer.name='example' 
  and (billing.lines_0_description like '%package1%' 
       or 
       billing.lines_0_description like '%package2%' 
       OR 
       billing.lines_0_description like'%package3%') 
group by customer.addresses_0_firstname, 
         customer.addresses_0_lastname, 
         customer.addresses_0_organisation, 
         customer.addresses_0_address1, 
         customer.addresses_0_address2, 
         customer.addresses_0_city, 
         customer.addresses_0_postcode, 
         customer.addresses_0_country, 
         customer.addresses_0_email, 
         billing.lines_0_description 

Que produz o seguinte:

AdminFName  AdminLName  CompanyName Address1    Address2    City    Zip CountryID   Email           Reseller    (No column name)
Test User   Testing     123         test        Rd          test    0   Test        [email protected]   true        2012-03-23
Test User   Testing     123         test        Rd          test    0   Test        [email protected]   true        2012-01-23

Que é uma lista de todos os dados do usuário (que queremos), mas unidos a cada linha de cobrança, em vez de selecionar a data_final com o maior valor.

Eu suspeito que tenho que fazer uma seleção aninhada, mas não tenho certeza da lógica que preciso aqui.

Sugestões?

Estou escrevendo uma consulta para executar no banco de dados SCOM 2012 OperationsManager. A instância SQL é Microsoft SQL Server 2012 - 11.0.5058.0 (X64).

Dentro do banco de dados há uma tabela chamada Monitors. O schema do DB pode ser encontrado aqui: Link to Schema

Posso executar a seguinte consulta:

select monitorid, configurationxml 
from dbo.monitor 
where monitorid = '4960E39A-59C8-A2C2-99B1-59B73D73156F'

e ele retorna isso:

monitorid                               configurationxml
4960E39A-59C8-A2C2-99B1-59B73D73156F    <ComputerName>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</ComputerName><DiskLabel>$Target/Property[Type="Windows!Microsoft.Windows.LogicalDevice"]/DeviceID$</DiskLabel><CounterName>PercentFree</CounterName><IntervalSeconds>900</IntervalSeconds><NumSamples>4</NumSamples><SystemDriveWarningThreshold>10</SystemDriveWarningThreshold><SystemDriveErrorThreshold>5</SystemDriveErrorThreshold><NonSystemDriveWarningThreshold>10</NonSystemDriveWarningThreshold><NonSystemDriveErrorThreshold>5</NonSystemDriveErrorThreshold>

(então o Monitor GUID e XML na colunaconfigurationxml

Posso então executar esta consulta:

Select 
    D.C.value('IntervalSeconds[1]','varchar(4000)') CheckInterval,
    D.C.value('NumSamples[1]','varchar(4000)') SamplesBeforeDown,
    D.C.value('SystemDriveWarningThreshold[1]','varchar(4000)') SystemDriveWarningThreshold,
    D.C.value('CounterName[1]','varchar(4000)') Countertype
    FROM (Select n.c.query('.') as xmlquery
    from
     (Select cast(ConfigurationXML as xml) Recxml
     FROM [dbo].[monitor] mt)
    a Cross Apply Recxml.nodes('/') N(C)) r
    Cross Apply xmlquery.nodes('/') D(C)

que fragmenta (não tenho certeza se esse é o termo certo) o XML e me permite recuperar valores de elementos específicos nessa coluna:

CheckInterval   SamplesBeforeDown   SystemDriveWarningThreshold Countertype

          900                   4                            10 PercentFree

O problema que tenho é que gostaria de poder extrair valores de elementos específicos vinculados ao GUID nessa linha específica para que eu possa ter o GUID e os valores de elementos XML específicos que desejo, algo que ficaria assim:

monitorid                            CheckInterval  SamplesBeforeDown   SystemDriveWarningThreshold Countertype

4960E39A-59C8-A2C2-99B1-59B73D73156F           900                  4                            10 PercentFree

O principal problema que estou encontrando é que, quando destruo o XML, ele perde sua referência à linha de onde veio.

Como posso vincular o conteúdo XML à linha de onde ele vem?