All perguntas(dba)

Tenho a seguinte dúvida com a qual estou brincando:

SELECT
    to_char(date_trunc('hour', "TimeStamp"), 'HH12 AM') || ' - ' || to_char(date_trunc('hour', "TimeStamp")  + interval '1 hour', 'HH12 AM') as time,
    COUNT(*) as totals
FROM 
    eventtotals
    
WHERE "TimeStamp" >= NOW() - Interval '24 HOURS'

GROUP BY date_trunc('hour', "TimeStamp")

Ele exibe a seguinte saída:

TIME                TOTAL
04 PM - 05 PM       300
05 PM - 06 PM       452
06 PM - 07 PM       393
07 PM - 08 PM       356
08 PM - 09 PM       356
09 PM - 10 PM       361
10 PM - 11 PM       359
11 PM - 12 AM       367
12 AM - 01 AM       357
01 AM - 02 AM       360
02 AM - 03 AM       286

Estou ciente do

TRIM(LEADING '0'...

No entanto, gostaria de remover os zeros à esquerda das horas. Só não tenho certeza de onde colocar a linha para corte na consulta listada acima, a menos que haja outra maneira.

Obrigado

Se...

INSERT INTO TABLE_NAME
    SELECT
        STRING_AGG(COLUMN_NAME, ',')
    FROM
        TABLE_NAME

introduz um antipadrão (ou seja, pode causar baixo desempenho, resultados incorretos (por favor, verifique isso) e problemas de manutenção das consultas T-SQL) ;

Que tal isto:

INSERT INTO TABLE_NAME(COLUMN_NAME)
    SELECT
        N'{"KEY_VALUE": [' + TBN.STR_AGG + '"]}' JSON_FORMAT_VALUE
    FROM
        (SELECT
             STRING_AGG('"' + COLUMN_NAME, + '", ') STR_AGG
         FROM
             TABLE_NAME) TBN

Esta é minha consulta de exemplo de teste real:

DECLARE @users TABLE(
    id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    username NVARCHAR(100) NOT NULL,
    email NVARCHAR(100) NOT NULL,
    status VARCHAR(50)
);

DECLARE @features TABLE(
    id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(200) NOT NULL,
    is_enabled BIT
);

DECLARE @feature_user TABLE(
    user_id INT NOT NULL,
    feature_id INT NOT NULL
);

INSERT INTO @users(
    username,
    email,
    status
)
VALUES(
    N'john_doe',
    N'[email protected]',
    'active'
),
(
    N'mark_daniels',
    N'[email protected]',
    'inactive'
),
(
    N'alice_jane',
    N'[email protected]',
    'active'
);

INSERT INTO @features(
    name,
    description,
    is_enabled
)
VALUES(
    'notifications',
    'Send notifications to users',
    'TRUE'
),
(
    'csv export',
    'Export data to CSV format',
    'FALSE'
),
(
    'redesign landing page',
    'Revamp the landing page layout',
    'TRUE'
);

INSERT INTO @feature_user
VALUES(
    1,
    1
),
(
    1,
    2
),
(
    1,
    3
),
(
    2,
    1
),
(
    2,
    2
),
(
    3,
    3
)

-- Produces comma-delimited data structure
SELECT 
    u.id AS user_id,
    u.username,
    u.email,
    u.status,
    STRING_AGG(f.name, ', ') AS feature_names
FROM @users u
LEFT JOIN @feature_user fu ON fu.user_id = u.id
LEFT JOIN @features f ON fu.feature_id = f.id
GROUP BY u.id, u.username, u.email, u.status;


-- Produces denormalized data structure
SELECT  
    feature_data.user_id,
    feature_data.username,
    feature_data.email,
    feature_data.status,
    N'{"feature_data": [' + feature_data.feature_names + '"]}' feature_names
    --ADD HERE THE RECORDS THAT YOU DON'T WANT TO BE DUPLICATED ROWS, WHERE YOU NEED TO USE THE SAME APPROACH WITH feature_names
FROM(
SELECT 
    u.id AS user_id,
    u.username,
    u.email,
    u.status,
    STRING_AGG('"' + f.name, + '", ') feature_names
    --ADD HERE THE RECORDS THAT YOU DON'T WANT TO BE DUPLICATED ROWS, WHERE YOU NEED TO USE THE SAME APPROACH WITH feature_names
FROM @users u
LEFT JOIN @feature_user fu ON fu.user_id = u.id
LEFT JOIN @features f ON fu.feature_id = f.id
GROUP BY u.id, u.username, u.email, u.status) feature_data;

-- The below query to check if the JSON data is valid
SELECT  
    ISJSON(N'{"feature_data": [' + feature_data.feature_names + '"]}') feature_names
    --ADD HERE THE RECORDS THAT YOU DON'T WANT TO BE DUPLICATED ROWS, WHERE YOU NEED TO USE THE SAME APPROACH WITH feature_names
FROM(
SELECT 
    STRING_AGG('"' + f.name, + '", ') feature_names
    --ADD HERE THE RECORDS THAT YOU DON'T WANT TO BE DUPLICATED ROWS, WHERE YOU NEED TO USE THE SAME APPROACH WITH feature_names
FROM @users u
LEFT JOIN @feature_user fu ON fu.user_id = u.id
LEFT JOIN @features f ON fu.feature_id = f.id
GROUP BY u.id, u.username, u.email, u.status) feature_data;

E as SELECTsaídas da consulta são:

Créditos ao dono da ideia: Como evitar linhas redundantes ao unir tabelas em SQL?

Entretanto, esse cenário de caso reflete a configuração do meu banco de dados.

Explicações: O JSON dataserá decodificado para uso posterior no banco de dados SQL Server com um compatibility level of 130 or greater, e estou usando compatibility level 140ou SQL Server 2017.

A aplicação dessa ideia quanto a se relacionar com o exemplo dado é ao concatenar dados dentro de uma única propriedade (por exemplo, conjunto de colunas específico) . Qual dos exemplos dados oferece muito performance efficientem termos de consideração do constantly growing database.

Além disso, alguém pode fornecer um conjunto de consultas para evitar antipadrões ?

Quero me aprofundar no esquema antipadrão para realmente entender como ele afeta as consultas.

ATUALIZAR:

DECLARE @compile_table_str_agg TABLE( --denormalizing data through comma-delimited data compilation
    user_id INT NOT NULL,
    username NVARCHAR(100) NOT NULL,
    email NVARCHAR(100) NOT NULL,
    status VARCHAR(50),
    feature_names VARCHAR(100) NOT NULL
);

DECLARE @compile_table_json TABLE( --denormalizing data through json-structure data compilation
    user_id INT NOT NULL,
    username NVARCHAR(100) NOT NULL,
    email NVARCHAR(100) NOT NULL,
    status VARCHAR(50),
    feature_names VARCHAR(100) NOT NULL
);

INSERT INTO @compile_table_str_agg
SELECT 
    u.id AS user_id,
    u.username,
    u.email,
    u.status,
    STRING_AGG(f.name, ', ') AS feature_names
FROM @users u
LEFT JOIN @feature_user fu ON fu.user_id = u.id
LEFT JOIN @features f ON fu.feature_id = f.id
GROUP BY u.id, u.username, u.email, u.status;

INSERT INTO @compile_table_json
SELECT  
    feature_data.user_id,
    feature_data.username,
    feature_data.email,
    feature_data.status,
    N'{"feature_data": [' + feature_data.feature_names + '"]}' feature_names
    --ADD HERE THE RECORDS THAT YOU DON'T WANT TO BE DUPLICATED ROWS, WHERE YOU NEED TO USE THE SAME APPROACH WITH feature_names
FROM(
SELECT 
    u.id AS user_id,
    u.username,
    u.email,
    u.status,
    STRING_AGG('"' + f.name, + '", ') feature_names
    --ADD HERE THE RECORDS THAT YOU DON'T WANT TO BE DUPLICATED ROWS, WHERE YOU NEED TO USE THE SAME APPROACH WITH feature_names
FROM @users u
LEFT JOIN @feature_user fu ON fu.user_id = u.id
LEFT JOIN @features f ON fu.feature_id = f.id
GROUP BY u.id, u.username, u.email, u.status) feature_data;

-- Retrieving the compiled STRING_AGG data
SELECT
    user_id,
    username,
    email,
    status,
    ca.*
FROM
    @compile_table_str_agg str_agg1
CROSS APPLY(
    SELECT
        com_delimited.*
    FROM
        STRING_SPLIT(
            feature_names, ',') com_delimited
) ca;

-- Retrieving the compiled JSON structure data
SELECT
    user_id,
    username,
    email,
    status,
    sa.ft_values
FROM
    @compile_table_json json1
CROSS APPLY
    OPENJSON(json1.feature_names) WITH(
            feature_data NVARCHAR(MAX) '$.feature_data' AS JSON
        ) ca
        CROSS APPLY OPENJSON(ca.feature_data) WITH (ft_values NVARCHAR(25) '$') sa;

E as UPDATE, SELECTsaídas da consulta são:

CONCLUSÃO: Ambos oferecem a mesma saída, mas qual oferece mais desempenho ou eficiência de manutenção, mesmo quando o banco de dados fica maior?

REFERÊNCIA DE CONSULTA JSON:

DECLARE @json NVARCHAR(MAX);

SET @json = '{"info": {"address": [{"town": "Belgrade"}, {"town": "Paris"}, {"town":"Madrid"}]}}';
SET @json = JSON_MODIFY(@json, '$.info.address[0].town', 'Philippines');

SELECT modifiedJson = @json;

DECLARE @json2 NVARCHAR(MAX);

SET @json2 = N'[
  {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},
  {"id": 5, "info": {"name": "Jane", "surname": "Smith"}, "dob": "2005-11-04T12:00:00"}
]';

SELECT *
FROM OPENJSON(@json2) WITH (
    id INT 'strict $.id',
    firstName NVARCHAR(50) '$.info.name',
    lastName NVARCHAR(50) '$.info.surname',
    age INT,
    dateOfBirth DATETIME2 '$.dob'
);

DECLARE @json3 NVARCHAR(MAX);

SET @json3 = N'[
  {"id": 3, "info": {"name": "John", "surname": "Smith"}, "age": 25},
  {"id": 5, "info": {"name": "Jane", "surname": "Smith", "skills": ["SQL", "C#", "Azure"]}, "dob": "2005-11-04T12:00:00"},
  {"id": 1, "info": {"name": "DevQt", "surname": "PH", "skills": ["Dart", "Java", "C#", "VB", "Javascript", "SQL"]}, "age": 26, "dob": "2005-11-04T12:00:00"}
]';

SELECT id,
    firstName,
    lastName,
    age,
    dateOfBirth,
    skill
FROM OPENJSON(@json3) WITH (
    id INT 'strict $.id',
    firstName NVARCHAR(50) '$.info.name',
    lastName NVARCHAR(50) '$.info.surname',
    age INT,
    dateOfBirth DATETIME2 '$.dob',
    skills NVARCHAR(MAX) '$.info.skills' AS JSON
)
OUTER APPLY OPENJSON(skills) WITH (skill NVARCHAR(15) '$')
ORDER BY id;

DECLARE @jsonVariable NVARCHAR(MAX);

SET @jsonVariable = N'[
  {
    "Order": {
      "Number":"SO43659",
      "Date":"2011-05-31T00:00:00"
    },
    "AccountNumber":"AW29825",
    "Item": {
      "Price":2024.9940,
      "Quantity":1
    }
  },
  {
    "Order": {
      "Number":"SO43661",
      "Date":"2011-06-01T00:00:00"
    },
    "AccountNumber":"AW73565",
    "Item": {
      "Price":2024.9940,
      "Quantity":3
    }
  }
]';

-- INSERT INTO <sampleTable>
SELECT SalesOrderJsonData.*
FROM OPENJSON(@jsonVariable, N'$') WITH (
    Number VARCHAR(200) N'$.Order.Number',
    Date DATETIME N'$.Order.Date',
    Customer VARCHAR(200) N'$.AccountNumber',
    Quantity INT N'$.Item.Quantity'
) AS SalesOrderJsonData;

E as JSONsaídas da consulta são:

Explicações adicionais: Incluí a referência no tratamento de JSON para que os outros vejam minha percepção de incorporar JSON com bancos de dados relacionais no SQL Server.

Referência original: Dados JSON no SQL Server

O banco de dados Prostgres está sendo executado no docker em um VPS (Ubuntu).

docker ps

4f945e0bf0bc postgres "docker-entrypoint.s…" 2 hours ago Up 2 hours 0.0.0.0:5432->5432/tcp, :::5432->5432/tcp postgres-0

De um PC com Windows, quero me conectar ao banco de dados usando o pgadmin, mas não consigo:

Unable to connect to server: connection timeout expired.

No campo "Nome/endereço do host" do pgadmin, inseri o endereço IP do servidor Ubuntu.

Veja a saída do comando "ip a"

O que mais preciso configurar para que o pgadmin possa acessar o banco de dados?

Tenho um banco de dados cheio de documentos, cada um contendo uma coleção de transações:

[
  {
    "key": 1,
    "data": [
      {
        "trans": 1,
        "uid": 1
      },
      {
        "trans": 2,
        "uid": 1
      }
    ]
  },
  {
    "key": 2,
    "data": [
      {
        "trans": 3,
        "uid": 1
      },
      {
        "trans": 4,
        "uid": 2
      }
    ]
  }
]

Quero criar um novo campo em cada um dos documentos principais, que tenha os valores exclusivos do uidcampo. Posso chegar a um ponto em que isso acontece usando $map:

db.collection.aggregate([
  {
    "$set": {
      "uid": {
        "$map": {
          "input": "$data",
          "as": "trans",
          "in": "$$trans.uid"
        }
      }
    }
  }
])

Isso me dá:

[
  {
    "key": 1,
    "uid": [1,1],
    "data": [
      {
        "trans": 1,
        "uid": 1
      },
      {
        "trans": 2,
        "uid": 1
      }
    ]
  },
  {
    "key": 2,
    "uid": [1, 2],
    "data": [
      {
        "trans": 3,
        "uid": 1
      },
      {
        "trans": 4,
        "uid": 2
      }
    ]
  }
]

Isso está próximo, mas não consigo descobrir o último passo. Quero usar apenas os valores exclusivos, então o uidpara o primeiro documento deve ser [1], não [1, 1].

A distinct()função funciona em coleções, não em documentos individuais.

Eu acho que isso $addToSetfuncionaria, mas não funciona em arrays, apenas na saída de $group. Também tentei criar uma $reduceespecificação usando $setUnion, mas não sei como promover meu valor numérico em um array.

Posso usar o $unwindestágio com agrupamento _idpara obter os valores corretos para o novo campo, mas não consigo descobrir como anexá-los de volta aos objetos originais.

Eu sei que posso bloquear uma tabela inteira usando LOCK TABLE, mas estou curioso para saber se posso obter o mesmo resultado usando a FOR UPDATEinstrução, supondo que todas as transações que atualizam essa tabela selecionam uma linha específica dessa tabela usando FOR UPDATEno início de sua transação (ou talvez tendo uma tabela separada chamada lockscom uma coluna exclusiva nomeada lock_namee usando SELECT lock_name FROM locks WHERE lock_name = 'foo' FOR UPDATEpara adquirir um bloqueio antes de cada transação)?

Estou fazendo essa pergunta porque preciso salvar uma estrutura de dados de árvore em uma tabela de banco de dados usando uma lista de adjacência ou uma tabela de fechamento. Quando vários usuários tentam adicionar um novo nó ou realocar uma subárvore, preciso selecionar algumas linhas e fazer alguns cálculos e, em seguida, atualizar a tabela com base nesses cálculos e essa operação não é possível a menos que eu bloqueie a tabela inteira.

Quero retornar minhas linhas de postagem em uma das três ordens de classificação, controladas por um sorting_paramparâmetro de função. A classificação é complexa, não se presta a um simples ORDER BY CASE .... Então, estou pensando em escrever cada classificação em seu próprio CTE e, em seguida, escolher um dos CTEs como a saída final. No entanto, não gostaria que o PostgreSQL perdesse tempo com todos os três, apenas com o que selecionei. Então, todos os três serão materializados ou apenas o que escolhi? Ou há uma maneira melhor de fazer isso? PostgreSQL versão 16. Código simplificado abaixo.

WITH posts_by_order1 AS
(
    SELECT p.id
    FROM post p
    ORDER BY [some complex ordering 1]
    LIMIT 10
),
posts_by_order2 AS
(
    SELECT p.id
    FROM post p
    ORDER BY [some complex ordering 2]
    LIMIT 10
),
posts_by_order3 AS
(
    SELECT p.id
    FROM post p
    ORDER BY [some complex ordering 3]
    LIMIT 10
)
SELECT * FROM posts_by_order1 WHERE sorting_param = 0
UNION ALL
SELECT * FROM posts_by_order2 WHERE sorting_param = 1
UNION ALL
SELECT * FROM posts_by_order3 WHERE sorting_param = 2;

Infelizmente não posso fazer isso:

    ORDER BY 
        CASE 
            WHEN sorting_param = 0 THEN p.date 
            WHEN sorting_param = 1 THEN p.name 
            ELSE NULL 
        END DESC, ...

Tenho o seguinte plano de execução:

Como você pode ver, as estimativas de linha para os operadores Clustered Index Scanand Index Seeksão precisas. No entanto, a junção Nested Loops tem uma discrepância significativa: a contagem real de linhas é 6.420, enquanto a contagem estimada de linhas é de apenas 72.

Minhas perguntas são:

1. Como a contagem de linhas é estimada para uma junção de Nested Loops no SQL Server?
2. Que fatores poderiam levar a uma estimativa de linha tão imprecisa neste caso?
3. Há algo que eu possa fazer para melhorar ou corrigir a estimativa?

Obrigado por qualquer informação!

Usando replicação transacional, um banco de dados criptografado (criptografia TDE) pode ser replicado para um destino não criptografado sem problemas?

Estou usando o MySQL 8.0.32. Tenho algumas perguntas sobre uma conversão interna, que não entendo. Além disso, às vezes essa conversão é mostrada em SHOW FULL PROCESSLIST, pois sua execução era muito longa.

Esta é a minha mesa.

CREATE TABLE `provinces` (
 `ID` VARCHAR(2) NOT NULL COLLATE 'utf8mb4_swedish_ci',
 `PROVINCIA` VARCHAR(45) NOT NULL COLLATE 'utf8mb4_swedish_ci',
  PRIMARY KEY (`ID`)
  ) COLLATE='utf8mb4_swedish_ci' ENGINE=InnoDB;

Eu tenho 53 linhas como essas

A função MySQL é:

CREATE FUNCTION `get_provincia`( `id_provincia` VARCHAR(2)  )
RETURNS varchar(45)
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE desc_provincia VARCHAR(45) DEFAULT '-';

    SELECT PROVINCIA INTO desc_provincia
    FROM provinces
    WHERE ID = id_provincia;
    
    RETURN desc_provincia;
END

O SHOW STATUS desta função mostra:

• character_set_client: utf8mb4
• conexão_de_agrupamento: utf8mb4_0900_ai_ci
• database_collation: latin1_swedish_ci

Essa função é invocada como um campo em uma consulta.

SELECT customer_id, customer_name, ..., get_provincia(SUBSTRING(zip_code,1,2))...
FROM ...

Quando estou revisando a execução com SHOW FULL PROCESSLIST, a consulta dentro da função é mostrada assim:

SELECT provincia INTO desc_provincia
FROM provincias
WHERE id =  NAME_CONST('id_provincia',_latin1'15' COLLATE 'latin1_swedish_ci')

Então, eu tenho essas perguntas:

1. Por que o NAME_CONST é forçado pelo MySQL?
2. Isso tem impacto no desempenho? Não entendo que uma consulta muito simples mostre o valor "2" na coluna "Time" do comando SHOW FULL PROCESSLIST, pois demora 2 segundos.

Desde já, obrigado.

Parece que meu entendimento do plano de consulta não está correto. Se eu executar esta consulta pela primeira vez e verificar o cache do plano de consulta. Vejo um plano em cache.

declare @codes varchar(max)='BHVD7,BHVDE,BHVDF'
SELECT OFST010020 AS [OFST010020], OFST730010 AS [OFST730010], Identifier AS [Identifier]   FROM STATIC_FUNDSHARECLASSUNIT_NEXT as f WITH(NOLOCK)  Inner join STATIC_FUNDSHARECLASSUNITEXTENSION_NEXT as fex WITH(NOLOCK) on fex.Identifier2 = f.Identifier    WHERE OFST900174 in (select value from string_split(@codes,','))

Se eu apenas alterar o parâmetro do código e executar a consulta novamente na mesma janela do SSMS, por exemplo

declare @codes varchar(max)='BHVD7,BHVDF'
SELECT OFST010020 AS [OFST010020], OFST730010 AS [OFST730010], Identifier AS [Identifier]   FROM STATIC_FUNDSHARECLASSUNIT_NEXT as f WITH(NOLOCK)  Inner join STATIC_FUNDSHARECLASSUNITEXTENSION_NEXT as fex WITH(NOLOCK) on fex.Identifier2 = f.Identifier    WHERE OFST900174 in (select value from string_split(@codes,',')) 

Achei que o plano de consulta teria 1 linha e o useCount seria 2. Mas no cache do plano de consulta, na verdade vi dois planos. O texto sql de cada plano é, na verdade, o texto completo (começando com declare @codes). Estou fazendo algo errado ou a consulta ad hoc não reutiliza realmente o plano, mesmo que o parâmetro esteja envolvido?