Perguntas[case](dba)

Minha tabela abaixo mostra as linhas e colunas onde os dados da coluna são filtrados usando uma sintaxe CASE para diferenciar capital de despesa. O problema é que estou recebendo várias linhas por project_ID. Eu preciso de uma linha por project_ID:

Eu preciso que a saída seja:

Aqui está meu Oracle SQl (que inclui muitas outras colunas não mostradas acima):

SELECT
    pi.project_id,
    pi.project_projectnumber, 
    pi.project_projectname, 
    cst.cost1,

    CASE WHEN bi.code = '01-00-000' THEN  cst.ubr_ROM_Approved
         ELSE NULL
    END AS CAPITAL_ROM,  

    CASE WHEN bi.code = '02-00-000' 
         THEN cst.ubr_ROM_Approved
         ELSE NULL
    END AS EXPENSE_ROM,  

    CASE WHEN bi.code = '01-00-000' 
         THEN cst.ubacf_CD_Approved
         ELSE NULL
    END AS CAPITAL_ADJ_CD,  

    CASE WHEN bi.code = '01-00-000' 
         THEN cst.ubacf_SD_Approved
         ELSE NULL
    END AS CAPITAL_ADJ_SD,  

    CASE WHEN bi.code = '01-00-000' 
         THEN cst.ubacf_DD_Approved
         ELSE NULL
    END AS CAPITAL_ADJ_DD,  

    CASE WHEN bi.code = '01-00-000' 
         THEN cst.ubacf_Budget_Approved
         ELSE NULL
    END AS CAPITAL_ADJ_Budget,  

    CASE WHEN bi.code = '02-00-000' 
         THEN cst.ubacf_CD_Approved
         ELSE NULL
    END AS EXPENSE_ADJ_CD,  

    CASE WHEN bi.code = '02-00-000' 
         THEN cst.ubacf_SD_Approved
         ELSE NULL
    END AS EXPENSE_ADJ_SD,  

    CASE WHEN bi.code = '02-00-000' 
         THEN cst.ubacf_DD_Approved
         ELSE NULL
    END AS EXPENSE_ADJ_DD,  

    CASE WHEN bi.code = '02-00-000' 
         THEN cst.ubacf_Budget_Approved
         ELSE NULL
    END AS EXPENSE_ADJ_Budget


FROM turnerb_upi_view pi
INNER JOIN turnerb_sys_project_info_view pv ON (pv.pid = pi.project_id)
INNER JOIN turnerb_us_p1_view prj ON (prj.pid = pi.project_id)
INNER JOIN turnerb_budget_summary_viewb cst ON (cst.projectid = pi.project_id)
INNER JOIN turnerb_ubr_view rom ON (rom.project_id = pi.project_id)
INNER JOIN turnerb_ubacf_view badj ON (badj.project_id = pi.project_id)
INNER JOIN turnerb_budgetitem bi ON (bi.budgetid = cst.budgetid)

WHERE pi.project_status = '1' 
AND rom.status ='ROM_Approved' 
AND (badj.status = 'SD_Approved' 
     OR badj.status = 'CD_Approved' 
     OR badj.status = 'DD_Approved' 
     OR badj.status = 'Budget_Approved')

GROUP BY 
    pi.project_id,
    pi.project_projectnumber, 
    pi.project_projectname, 
    bi.code,
    cst.cost1,
    cst.ubr_ROM_Approved,
    cst.ubacf_CD_Approved,
    cst.ubacf_SD_Approved,
    cst.ubacf_DD_Approved,
    cst.ubacf_Budget_Approved

ORDER BY project_projectnumber;

Atualmente estou usando mysql para retornar a soma de todos os dados em uma coluna que tem a mesma palavra. Para isso estou usando a seguinte consulta:

SELECT SUM(CASE WHEN status_to = 'Draft' THEN 1 END) AS draft, 
SUM(CASE WHEN status_to = 'Unpublish' THEN 1 END) AS unpublish, 
SUM(CASE WHEN status_to = 'Publish' THEN 1 END) AS publish, 
SUM(CASE WHEN status_to = 'Action' THEN 1 END) AS action, 
SUM(CASE WHEN status_to = 'Unlisted' THEN 1 END) AS unlisted, 
SUM(CASE WHEN status_to = 'Sold' THEN 1 END) AS sold, 
SUM(CASE WHEN status_to = 'Let' THEN 1 END) AS let 
FROM `crm_logs`

Isso fornece a saída correta no meu banco de dados para todos os termos que especifiquei, mas agora descobri que há mais variáveis ​​de status no banco de dados além das especificadas acima. Então eu quero uma forma de ter a mesma funcionalidade dessa declaração, mas tornar a variável de status dinâmica.

Basicamente se esta for a consulta SUM(CASE WHEN status_to = 'Draft' THEN 1 END) AS draft, ambas as ocorrências de rascunho devem ser dinâmicas.

No livro Getting Started with SQL , Thomas Nield fala sobre uma técnica que ele chama de truque de caso zero/nulo :

Existe uma ferramenta simples, mas poderosa, que pode aplicar diferentes condições de filtragem a diferentes agregações. Podemos criar contagens totais separadas quando um tornado estava presente versus não presente em duas colunas separadas:

SELECT year, month,
SUM(CASE WHEN tornado = 1 THEN precipitation ELSE 0 END) as tornado_precipitation,
SUM(CASE WHEN tornado = 0 THEN precipitation ELSE 0 END) as non_tornado_precipitation
FROM station_data
WHERE year >= 1990
GROUP BY year, month

O que fizemos efetivamente foi nos livrar das condições WHERE quando tornado = 1 ou tornado = 0 e, em seguida, mover essas condições para expressões CASE dentro das funções SUM(). Se a condição for atendida, o valor da precipitação é adicionado à soma. Caso contrário, um 0 é adicionado, sem efeito. Fazemos isso para ambas as colunas, uma para quando um tornado estava presente e outra para quando um tornado não estava presente, respectivamente.

Você pode fazer com que a expressão CASE tenha quantos pares de condição/valor forem necessários, dando a você a capacidade de fazer interceptações de valores altamente particulares com suas agregações. Você também pode usar esse truque para emular crosstabs e tabelas dinâmicas, expressando agregações em colunas separadas em vez de linhas. Um exemplo comum disso é fazer a análise do ano atual/ano anterior, pois você pode expressar anos separados com colunas diferentes.

Como iniciante, essa técnica parece ser muito útil para resumir dados. Quero pesquisar essa técnica on-line para encontrar outras técnicas semelhantes em livros ou outros recursos.

O autor desse livro chama a técnica de "truque de caso zero/nulo". Mas quando eu google esse termo, não recebo muitos resultados.

Pergunta:

Existe um nome geralmente aceito para essa técnica? (que traria mais resultados ao pesquisar online)

O código que estou usando está aqui

CREATE TABLE timecard (
   id INTEGER PRIMARY KEY autoincrement,
   clock_in REAL not null,
   clock_out REAL,
   lunch integer,
   pay_type TEXT
);
INSERT INTO timecard VALUES(1,julianday("2021-02-15 08:00"),julianday("2021-02-15 16:00"),0,"HOLIDAY");
INSERT INTO timecard VALUES(2,julianday("2021-02-16 07:23"),julianday("2021-02-16 16:49"),1,"WORK");
INSERT INTO timecard VALUES(3,julianday("2021-02-17 07:16"),julianday("2021-02-17 16:05"),1,"WORK");
INSERT INTO timecard VALUES(4,julianday("2021-02-18 07:54"),julianday("2021-02-18 15:48"),1,"WORK");

.header on
.mode column

SELECT strftime('%W',clock_in) AS 'weekofyear',
       time(clock_in) as "IN",
       time(clock_out) as "OUT",
       CASE when lunch = 1 then round(Cast (( JulianDay(clock_out) - JulianDay(clock_in)) * 24 - .5 As REAL),2)
            when lunch = 0 then round(Cast (( JulianDay(clock_out) - JulianDay(clock_in)) * 24 As REAL),2)
       END as "total_time",
       sum("total_time")
FROM timecard
GROUP BY "weekofyear";

Esta é a saída do comando.

weekofyear  IN          OUT         total_time  sum("total_time")
----------  ----------  ----------  ----------  -----------------
07          08:00:00    16:00:00    8.0         0.0

Estou tentando obter o total de horas para a semana do ano. Não tenho certeza do que estou fazendo de errado. Eu sou novo na instrução case em SQL e SQL em geral.

Obrigado pelo seu tempo

Vamos fazer uma consulta diária com uma cláusula CASE para que eu possa mostrar o problema, veja como teríamos que mudar a consulta quando uma nova Certificação sair:

select count(*) as "certCount",
CASE
when c.id = 1 then 'AWS Cloud Practitioner'
when c.id = 2 then 'AWS Alexa Skill Builder'
when c.id = 3 then 'AWS Solution Architect Associate'
when c.id = 4 then 'AWS Developer Associate'
when c.id = 5 then 'AWS SysOps Associate'
when c.id = 6 then 'AWS Solution Architect Professional'
when c.id = 7 then 'AWS DevOps Professional'
when c.id = 8 then 'AWS Security'
when c.id = 9 then 'AWS Networking'
when c.id = 10 then 'AWS Big Data'
when c.id = 11 then 'AWS Machine Learning'
ELSE 'N/A'
END AS name
from certification c
inner join qualification q on c.id = q.certificationid
group by q.certificationid, c.id

Claro que poderíamos gerar o SQL e executá-lo de forma Ad-Hoc. Isso é o que eu fiz no passado, embora eu esteja querendo saber se existe outra maneira, como alguma maneira de ingressar que evite os CASE WHENs?

Esquema e dados de amostra abaixo, estão no PostGres, mas também estou interessado em instruções de caso dinâmico para Oracle, SQLServer, etc:

 CREATE TABLE certification (
    id serial NOT NULL,
    officialcertname text NOT NULL,
    "name" text NOT NULL,
    vendorid int4 NOT NULL DEFAULT 1,
    isdeleted bool NOT NULL DEFAULT false,
    CONSTRAINT certification_pkey PRIMARY KEY (id)
);

CREATE TABLE qualification (
    id serial NOT NULL,
    employeeid int4 NOT NULL,
    certificationid int4 NOT NULL,
    date_attained timestamptz NULL,
    date_expiry timestamptz NULL,
    certurl text NULL,
    verified bool NOT NULL DEFAULT false,
    created_by text NOT NULL,
    created_date timestamptz NOT NULL,
    modified_by text NULL,
    modified_date timestamptz NULL,
    CONSTRAINT qualification_pkey PRIMARY KEY (id)
);


INSERT INTO certification (officialcertname,"name",vendorid,isdeleted) VALUES 
('AWS Certified Cloud Practitioner (CLF)','AWS Cloud Practitioner',1,false)
,('AWS Certified Alexa Skill Builder','AWS Alexa Skill Builder',1,false)
,('AWS Certified Solutions Architect - Associate (SAA)','AWS Solution Architect Associate',1,false)
,('AWS Certified Developer - Associate (DVA)','AWS Developer Associate',1,false)
,('AWS Certified SysOps Administrator - Associate (SOA)','AWS SysOps Associate',1,false)
,('AWS Certified Solutions Architect - Professional (SAP)','AWS Solution Architect Professional',1,false)
,('AWS Certified DevOps Engineer - Professional (DOP)','AWS DevOps Professional',1,false)
,('AWS Certified Security - Specialty (SCS)','AWS Security',1,false)
,('AWS Certified Networking - Specialty (SNS)','AWS Networking',1,false)
,('AWS Certified Big Data - Specialty','AWS Big Data',1,false)
;

Eu sou novo no MySQL e esta pergunta pode parecer boba, mas:

Eu tenho uma dogstabela e categorizo ​​seu tamanho por seu peso:

SELECT *,
    CASE
        WHEN weight < 15 THEN 'Small'
        WHEN weight < 40 THEN 'Medium'
        ELSE 'Big'
    END AS Size
FROM dogs;

Isso gera minha tabela com uma nova coluna Sizeno final. E eu gostaria de anexar permanentemente essa coluna a esta tabela para que fique visível quando eu usarSELECT * FROM dogs;

eu preciso de ajuda por favor com a seguinte consulta. Estou puxando os códigos postais ( plz) junto com o ID do usuário ( uid) do banco de dados e ORDER por zip (plz) e uid. Também uso uma variável definida pelo usuário @prevpara verificar se previous plz é diferente de current plz. Nesta coluna CASE rownumdeve diminuir a variável inicial definida @rowpelo usuário em 1.

Aqui está a minha consulta para isso ..

SELECT   
  @prev as previous, @prev := u.plz as current, 
  CASE 
    WHEN @prev is null THEN @row := @row -1 
    WHEN @prev <> u.plz THEN @row := @row -1
    ELSE @row END AS rownum, 
  uid

FROM             
  (SELECT @prev := NULL, @row := 1001) as r, user as u

LEFT JOIN 
  geodb_locations ON u.plz = geodb_locations.id 
WHERE 
  u.plz IN(29386,30013,29271,30406) AND
ORDER BY 
  FIELD(u.plz,29386,30013,29271,30406), 
  uid DESC

Resultado

+----------+---------+--------+-------+
| previous | current | rownum | uid   |
+----------+---------+--------+-------+
| NULL     |   29386 |   1001 | 92014 | <-- @prev <> u.plz why NOT decreasing `rownum`
| 29386    |   29386 |   1001 | 87223 |
| 29386    |   29386 |   1001 | 83160 |
| 29386    |   29386 |   1001 | 81322 |
| 29386    |   29386 |   1001 | 81293 |
| 29386    |   29386 |   1001 |  3152 |
| 29386    |   29386 |   1001 |  1939 |
| 29386    |   30013 |   1001 | 65193 | <-- @prev <> u.plz why NOT decreasing `rownum`
| 30013    |   29384 |   1001 | 12883 |
| 29384    |   29385 |   1001 | 32667 |
| 29385    |   29232 |   1001 | 86641 |
| 29232    |   29481 |   1001 | 94521 |
| 29481    |   29481 |   1001 | 33950 |
| 29481    |   29481 |   1001 | 33926 |
| 29481    |   29667 |   1001 | 16027 |
| 29667    |   29667 |   1001 |   496 |
| 29667    |   29355 |   1001 | 96013 |
+----------+---------+--------+-------+

Saída esperada

+----------+---------+--------+-------+
| previous | current | rownum | uid   |
+----------+---------+--------+-------+
| NULL     |   29386 |   1000 | 92014 | <-- decrease @row `rownum` by one
| 29386    |   29386 |   1000 | 87223 |
| 29386    |   29386 |   1000 | 83160 |
| 29386    |   29386 |   1000 | 81322 |
| 29386    |   29386 |   1000 | 81293 |
| 29386    |   29386 |   1000 |  3152 |
| 29386    |   29386 |   1000 |  1939 |
| 29386    |   30013 |    999 | 65193 | <-- decrease @row `rownum` by one 
| 30013    |   29384 |    998 | 12883 |
| 29384    |   29385 |    997 | 32667 |
| 29385    |   29232 |    996 | 86641 |
| 29232    |   29481 |    995 | 94521 |
| 29481    |   29481 |    995 | 33950 |
| 29481    |   29481 |    995 | 33926 |
| 29481    |   29667 |    994 | 16027 |
| 29667    |   29667 |    994 |   496 |
| 29667    |   29355 |    993 | 96013 |
+----------+---------+--------+-------+

Por que CASE WHEN não está funcionando como esperado? Onde está meu erro de pensar? Desde já, obrigado ..

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.

como escrever uma expressão case com o Postgres? eu tenho isto

case
  when rc.stat = 0 Then unpaid
  when rc.stat = 1 Then paid
  when rc.stat = 2 Then dispute
  else rc.stat
end

mas quando vou executar a declaração recebo um erro de

Error: column "unpaid" does not exist

Eu também tentei aspas duplas e colchetes em torno dos valores de texto, mas todas essas opções me dão erros. Apenas para observar, os valores não pagos, pagos, contestados NÃO são campos no banco de dados, mas valores de texto que quero escrever.

Eu tenho duas condições que estão fazendo com que a instrução CASE não funcione conforme o esperado. Eu tentei mudar qual condição vem primeiro, mas ainda assim só prossegue com uma delas. Alguma sugestão?

Instrução CASE que retorna apenas o valor antes do primeiro '(' e substitui todas as instâncias de 'Metallic' por 'M' :

CASE
WHEN `order_option`.`value` LIKE '%(%)%' THEN SUBSTRING_INDEX(`order_option`.`value`,' (', 1)
WHEN `order_option`.`value` LIKE '%Metallic%' THEN REPLACE (`order_option`.`value`,'Metallic','M')
ELSE `order_option`.`value`
END AS `Option`,

Dados atuais

`value`
--------
Red Metallic (999)
Blue (745)

Resultados esperados

`Option`
--------
Red M
Blue

Agradeço antecipadamente!