Dev Faq
0
votes

Colonne de groupe par des entrées uniques dans une autre colonne SQL

J'ai le jeu de données suivant

Entrez la description de l'image ici

Je voudrais interroger les données pour produire une liste de noms d'hôte uniques par nom d'utilisateur avec le dernier identifiant Le temps pour cet enregistrement est également inclus. Par exemple, produire le jeu de données suivant.

Entrez la description de l'image ici

L'objectif est de détecter le partage de compte des utilisateurs, ainsi que des utilisateurs ayant un nombre anormalement élevé de Noms d'hôte.

Je sais assez de SQL pour me procurer des ennuis, mais je n'écris tout simplement pas assez souvent pour être assez compétent pour écrire celui-ci sans souffler une demi-journée. Quelqu'un peut-il aider?

Nous utilisons Azure SQL (SQL Server), mais je peux traduire des réponses d'une autre langue SQL.

merci

Mise à jour

J'ai utilisé ce qui suit xxx

qui renvoie un bon jeu de données, mais lorsque j'essaie ce qui suit, il renvoie 0 enregistrements malgré la Query ci-dessus montrant plusieurs noms d'utilisateur sur le même nom d'hôte xxx

1 commentaires

Dans votre deuxième partie de la question: "Sélectionnez Nom d'utilisateur, Nom d'utilisateur, Max (LogintiMeTC) auprès du groupe Nom d'utilisateur, Nom d'utilisateur comptant (Distinct (Nom d'hôte))> 1" Vous vérifiez des combinaisons uniques de [Nom d'utilisateur, HostName]. Et dans ce résultat, vous ne seriez pas d'enregistrement avec le nombre (distinct (nom d'hôte))> 1

4 Réponses :

0
votes

Je voudrais interroger les données pour produire une liste de noms d'hôte uniques par nom d'utilisateur avec le dernier temps de connexion pour cet enregistrement également inclus.

Je pense que vous voulez juste groupe par : xxx

4 commentaires

Je veux filtrer, donc je ne vois que le dernier identifiant pour un nom d'hôte donné, je ne veux pas voir le même nom d'utilisateur / nom d'hôte des milliers de fois.

@Rouleaux . . . Cela montre une paire de nom d'utilisateur / nom d'hôte exactement une fois, donc je ne comprends pas votre commentaire. Si vous voulez la dernière connexion pour un nom d'hôte donné, pourquoi vos résultats ont-ils deux rangées pour Host2?

Je ne veux pas la dernière connexion. Je souhaite détecter le partage de compte en montrant plusieurs noms d'utilisateur utilisés sur le même nom d'hôte. Je veux aussi voir quand un nom d'utilisateur a été utilisé sur un grand nombre de noms d'hôte

@Rouleaux . . . Vous décrivez votre problème comme "Je voudrais interroger les données pour produire une liste de noms d'hôte uniques par nom d'utilisateur avec le dernier temps de connexion pour cet enregistrement également inclus." groupe par fait exactement cela. Si vous avez un problème différent, vous devez demander à une question nouvelle .

0
votes

Vous pouvez utiliser Row_Number () pour cela. 

select * from table1 t1
inner join
    (select row_number() over (partition by HostName, UserName order by LoginTimeUTC desc) as rn, UserName
            ,LoginTimeUTC, HostName from table1) as t2
on t2.UserName = t1.UserName and t2.LoginTimeUTC = t2.LoginTimeUTC and t2.HostName = t1.HostName
where t2.rn = 1

0 commentaires
0
votes

Si je comprends bien, 2 résultats sont attendus sans envisager l'heure de connexion, les PLS ont un essai inférieur à la requête: 

select username,hostname,
count(*) over (partition by hostname) as NUMBER_OF_USERS_FOR_THIS_HOST,
count(*) over (partition by username) as NUMBER_OF_HOSTS_FOR_THIS_USER
from loginrecords
group by username, hostname;

0 commentaires
0
votes

D'abord, j'ai créé un environnement de test en utilisant les requêtes ci-dessous. Ce serait bien si vous fournissez vous-même ces données (ou des données tabulaires textuelles) dans les prochaines questions. Les captures d'écran avec des données sont très hostiles à des fins de test. 

username      hostname      (No column name)
user1         host1         22/08/2019 9:51
user1         host2         30/08/2019 10:51
user2         host2         25/08/2019 9:51
user2         host3         30/08/2019 9:51
user3         host4         30/08/2019 10:51
user3         host5         25/08/2019 5:51
user4         host6         30/08/2019 10:51
user4         host7         30/08/2019 5:51

0 commentaires

Articles qui pourrait vous intéresser :

Ordre des requêtes de syntaxe SQL par
Problème de jointure sql, impossible de récupérer les enregistrements de la table a qui correspondent à une condition de la table b et les enregistrements restants de la table a
Interroger les données de PostgreSQL à partir de deux tables
Comment identifier les lignes par groupe avant un certain écart de valeur?