sql server - Efficient Amazon style 'customer also viewed' SQL Query -

-

i figured out need record client session id , viewed product id in order acheive this. here table script:

create table [dbo].[pagevisits](     [id] [int] identity(1,1) not null,     [xsession] [int] null,     [xpagename] [int] null ) on [primary]

i've come query seems work ok

select top 5 xpagename, count(xpagename)as total  pagevisits  pagevisits.xsession in (select pagevisits.xsession                                pagevisits \                               pagevisits.xpagename = 20                                , pagevisits.xsession != 539447381 )  , pagevisits.xpagename != 20  group xpagename  order total desc

however being bit of novice @ sql i'm fearful table grow quite large, choke server? there better way or ok?

i not sure why need inner select there. if want select pages visited customer in existing session (assumption: session id known) other current page, can:

select top 5 xpagename, count(xpagename)as total  pagevisits  pagevisits.xsession = 539447381 , pagevisits.xpagename != 20 group xpagename  order total desc

where 539447381 current session id , 20 current page.

you can improve performance of if can add clustered index on xpagename , xsession:

create clustered index [custom] on [dbo].[pagevisits]  (     [xsession] asc,     [xpagename] asc )with (statistics_norecompute  = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks  = on, allow_page_locks  = on) on [primary] go

ps: question title said "customer viewed" , answer. if expected result returning top 5 pages visited other customers viewed current page, query given in question sufficient add clustered index better performance (replaces table scans clustered index seeks).


Comments

Post a Comment

Popular posts from this blog

sql server - MSSQL Text and Varchar(MAX) fields shown (MEMO) in DBGrid -

-
Image
i have encountered problem in mssql & delphi xe3. text , varchar(max) fields seemed (memo) or (widememo) in tdbgrid & tdbadvgrid (tms dbgrid). in first screenshot can see table fiels, in second screentshot can see sample data in database: in screenshot, see how in dbgrid or in query results. i figured out changing field type char , varchar not max solves issue don't know why having problem. can please let me know?
Read more

qml - Is it possible to implement SystemTrayIcon functionality in Qt Quick application -

-
i writing qtquick desktop application. use both c++ (for functionality) , qml (for ui) in it. use qquickview show interface written in qml. i want application reside in system tray when minimised. i mean functionality similar example. http://qt-project.org/doc/qt-4.8/desktop-systray.html . trying implement feature not find way in qt quick application. here main.cpp code: #include <qguiapplication> #include <qqmlengine> #include <qqmlcontext> #include <qqmlfileselector> #include <qquickview> #include "myapp.h" int main(int argc, char* argv[]) { qguiapplication app(argc,argv); app.setapplicationname(qfileinfo(app.applicationfilepath()).basename()); qdir::setcurrent(qapp->applicationdirpath()); myapp myappobject; qquickview view; view.connect(view.engine(), signal(quit()), &app, slot(quit())); view.rootcontext()->setcontextproperty("myappobject", &myappobject); new qqmlfilese...
Read more

mysql - Flyway migration, Unable to obtain Jdbc connection from DataSource -

-
i trying use flyway create , manage mysql database. here code have got far. flywaymigration.java : class applys migration public class flywaymigration { public flywaymigration(databaseconfiguration configuration, flyway flyway) { flyway.setdatasource(configuration.getdatasource()); flyway.migrate(); } public static void main(string[] args) { new flywaymigration(new databaseconfiguration("database.properties"), new flyway()); } } databaseconfiguration.java : configuration class, class configure datasource applyed flyway.setdatasource method public class databaseconfiguration { private final logger logger = loggerfactory.getlogger(this.getclass()); private propertiesutil prop = null; public databaseconfiguration(string file) { prop = new propertiesutil(file); } public string getdatasourceclass() { return prop.getproperty("mysql.data.source.class"); } pu...
Read more