Selecting unique logins in a given time period
My SQL is a little rusty and JPQL is entirely new to me. I have a table with recorded login events, which have a user id and a time. I'm trying to select the unique number of user id's present in a selection of login events that occured in a specified time period:
Query query = em.createQuery(
"SELECT COUNT(*) FROM ( " +
"SELECT DISTINCT s.userId FROM UserSession s " +
"WHERE s.loginTime >= :fromTime " +
" AND s.loginTime < :toTime " +
") "
);
query.setParameter("fromTime", new Date(fromTime));
query.setParameter("toTime", new Date(toTime));
This give开发者_开发技巧s me a parsing error (the (
after FROM
is unexpected) so it's apparently not the way to go. What should I do instead?
Example: If I have the records {A,B,B,A,A,C,A,B,D} all within the date range, I'd like the query to return 4.
Query query = em.createQuery(
"SELECT COUNT(DISTINCT s.userId) FROM UserSession s " +
"WHERE s.loginTime >= :fromTime " +
" AND s.loginTime < :toTime " +
);
Source: http://docs.jboss.org/hibernate/entitymanager/3.5/reference/en/html/queryhql.html#queryhql-aggregation
精彩评论