sql - Select Top User over a list of Pages -


i have table containing records of users' internet history. table's structure contains user_id, page accessed, , date accessed of page. example:

+==========================================+  |user_id  | page_accessed | date_accessed  | +==========================================+ |johh.doe | google        |    1/1/2015    | |johh.doe | google        |    1/1/2015    | |suzy.lue | google        |    7/11/2015   |  |suzy.lue | wikipedia     |    4/23/2015   | |babe ruth| stackoverflow |    9/1/2015    | +==========================================+

i trying use sql query uses:

rank() on (partition [page accessed] order count(dateacc))

then use pivot() various sites. after selecting records where (num = 1) pivot() , group [rank], i'm ending resulting query similar to:

+=================================================+  |rank     | google  | wikipedia  |  stackoverflow | +=================================================+ |   1     | john doe|    null    |     null       | |   1     |   null  |  suzy lue  |     null       | |   1     |   null  |    null    |     babe ruth  |  +=================================================+

instead need reformat output as:

+=================================================+  |rank     | google  | wikipedia  |  stackoverflow | +=================================================+ |   1     | john doe| suzy lue   |   babe ruth    | +=================================================+

my current query:

select rank, google, wikipedia, stackoverflow     from(         select top (100) percent user_id, page_accessed, count(date_accessed) views,          rank() on (partition page_accessed order count(date_accessed) desc) rank            record_table         group dbo.location_key.subsite, dbo.user_info_list_parse.name     order views desc) tb       pivot (     max(tb.user_id)      page_accessed in ( google, wikipedia, stackoverflow)     ) pvt  (num = 1)

are there creative solutions obtain result?

i think you've found solution information , others reading - let me erase noise in query. there no need order by, no need apply top (100) percent, views column redundant. simplify query follows:

create table internethistory (     [user_id] varchar(20),     [page_accessed] varchar(20),     [date_accessed] datetime ) insert internethistory values ('johh.doe', 'google', '2015-01-01'), ('johh.doe', 'google', '2015-01-01'), ('suzy.lue', 'google', '2015-07-11'), ('suzy.lue', 'wikipedia', '2015-04-23'), ('babe ruth', 'stackoverflow', '2015-01-09')  select * (     select [user_id], [page_accessed], rank() on (partition [page_accessed] order count(*) desc) ranking     internethistory     group [user_id], [page_accessed] ) src pivot (     max([user_id]) [page_accessed] in ([google], [wikipedia], [stackoverflow]) ) pvt ranking = 1

Comments

Post a Comment

Popular posts from this blog

jOOQ update returning clause with Oracle -

according http://www.jooq.org/doc/3.8/manual/sql-building/sql-statements/update-statement/ jooq doesn't support returning clause in update statements databases except firebird , postgres. does know, still correct oracle jooq? according oracle's documentation, oracle db supports returning clase delete, insert, or update statements. incorrect manual you're right - manual wrong , should fixed: https://github.com/jooq/jooq/issues/5470 single row update returning jooq 3.8 added support single row update .. returning statements. internally, uses callablestatement , pl/sql block of form: begin update "my_table" set "my_table"."column" = 'xyz' "my_table"."id" = 1 returning "my_table"."id", "my_table"."column" ?, ?; ? := sql%rowcount; end; the relevant issue is: https://github.com/jooq/jooq/issues/5190 multi row update returning a...
Read more

java - Warning equals/hashCode on @Data annotation lombok with inheritance -

i have entity inherits other. on other hand, i'm using lombok project reduce boilerplate code, put @data annotation. annotation @data inheritance produces next warning: generating equals/hashcode implementation without call superclass, though class not extend java.lang.object. if intentional, add '@equalsandhashcode(callsuper=false)' type. is advisable add annotation @equalsandhashcode (callsuper = true) or @equalsandhashcode (callsuper = false) ? if not added, 1 callsuper=false or callsuper=true ? the default value false . 1 if don't specify , ignore warning. yes, recommended add @equalsandhashcode annotation on @data annotated classes extend else object. cannot tell if need true or false , depends on class hierarchy, , need examined on case-by-case basis. however, project or package, can configure in lombok.config call super methods if not direct subclass of object. lombok.equalsandhashcode.callsuper = call for configuration system ...
Read more

java - BasicPathUsageException: Cannot join to attribute of basic type -

i using java 8 jpa (hibernate 5.2.1). have clause works perfectly, until introduce clause make use foreign key table values too. i getting following error: basicpathusageexception: cannot join attribute of basic type i think problem related fact have join table, , not sure how create join using join table. employee - employee_category - category model (employee.java): @manytomany(cascade = cascadetype.all, fetch=fetchtype.eager) @jointable ( name="employee_category", joincolumns={ @joincolumn(name="emp_id", referencedcolumnname="id") }, inversejoincolumns={ @joincolumn(name="cat_id", referencedcolumnname="id") } ) private set<category> categories; model (category.java): @id private string id; jpa when introduce following code, fails error above (i think problem new piece of code): join<t, category> category = from.join("id"); predicates.add(criteriabuilder.like(category....
Read more