Início / dba / Perguntas / 107907
Accepted
amatusko
Asked: 2015-07-24 07:24:05 +0800 CST

Selecionando max (data) da consulta de união

  • 772

Estou tendo um pouco de dificuldade em obter resultados precisos de uma consulta que me parece que deveria funcionar. RDBMS é MySQL.

Estou tentando obter a data máxima do último login de uma seleção de união em três bancos de dados para produtos Atlassian JIRA, Confluence e Stash. Deve haver apenas uma única entrada nos resultados para cada usuário (o max(Last_Login) deve obter a data máxima do último login e mostrar um resultado se esse max(Last_Login) for mais antigo que hoje - 90 dias.

Resumi a saída da consulta de união e, em seguida, seleciono a partir desse agregado para que eu possa obter a data máxima de login que é 90 dias atrás ou antes.

O problema: a consulta parece funcionar na maior parte, mas há alguns casos em que um usuário mostra atividade nas interfaces Confluence, Stash ou JIRA que são mais recentes do que as que tenho aqui.

Aqui está a consulta que estou tentando usar:

select
    User_Name,
    First_Name,
    Last_Name,
    max(Last_Login) as Last_Login
FROM
(
SELECT
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name,
    ifnull(from_unixtime((cast(a.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM jira.cwd_user u
  LEFT JOIN jira.cwd_user_attributes a ON u.id = a.user_id AND a.attribute_name = 'login.lastLoginMillis'
  JOIN jira.cwd_directory d ON u.directory_id = d.id
where u.active = 1
UNION
select
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name, 
    ifnull(from_unixtime((cast(ca.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM confluence.cwd_user u
  LEFT JOIN confluence.cwd_user_attribute ca ON u.id = ca.user_id AND ca.attribute_name = 'login.lastLoginMillis'
  JOIN confluence.cwd_directory d ON u.directory_id = d.id
where u.active = 'T'
UNION
select
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name,
    ifnull(from_unixtime((cast(sa.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM stash.cwd_user u
LEFT JOIN stash.cwd_user_attribute sa ON u.id = sa.user_id AND sa.attribute_name = 'login.lastLoginMillis'
JOIN stash.cwd_directory d ON u.directory_id = d.id
where u.is_active = 'T'
) as z
group by User_Name, First_Name, Last_Name
having (max(Last_Login)) <= date_sub(now(), interval 90 day)
order by User_Name asc, Last_Login desc
mysql union

1 respostas

  1. Best Answer

    Acontece que a confluência usa uma estrutura diferente para gerenciar esses dados e agora as discrepâncias parecem ter sido resolvidas.

    Aqui está a consulta:

    select
    User_Name,
    First_Name,
    Last_Name,
    max(Last_Login) as Last_Login
FROM
(
SELECT
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name,
    ifnull(from_unixtime((cast(a.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM jira.cwd_user u
  LEFT JOIN jira.cwd_user_attributes a ON u.id = a.user_id AND a.attribute_name = 'login.lastLoginMillis'
  JOIN jira.cwd_directory d ON u.directory_id = d.id
where u.active = 1
UNION
 SELECT 
    u.user_name, 
    u.first_name as First_Name, 
    u.last_name as Last_Name, 
    date_format(l.successdate, '%Y-%m-%d') as Last_Login 
  FROM confluence.logininfo l 
  JOIN confluence.user_mapping m ON m.user_key = l.username 
  JOIN confluence.cwd_user u ON m.username = u.user_name 
  JOIN confluence.cwd_directory d ON u.directory_id = d.id and u.active = 'T' 
UNION
select
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name,
    ifnull(from_unixtime((cast(sa.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM stash.cwd_user u
LEFT JOIN stash.cwd_user_attribute sa ON u.id = sa.user_id AND sa.attribute_name = 'login.lastLoginMillis'
JOIN stash.cwd_directory d ON u.directory_id = d.id
where u.is_active = 'T'
) as z
group by User_Name, First_Name, Last_Name
having (max(Last_Login)) <= date_sub(now(), interval 90 day)
order by User_Name asc, Last_Login desc
    • 1

relate perguntas