edit: luke.breuer.com search term analysis

links

history

item nametags

==common searches by phrase and word
{{
uri_stem                                         count term_top_N term_agg                                                             word_top_N word_agg                                                              
------------------------------------------------ ----- ---------- -------------------------------------------------------------------- ---------- ----------------------------------------------------------------------
tutorial: javascript-modal-dialog                5535  1915       javascript modal (266), javascript modal dialog (556), javascript... 12862      dialog (2043), javascript (4057), modal (4701), popup (988), wind...  
tutorial: javascript-drag-and-drop-tutorial      4971  1777       drag and drop javascript (102), javascript drag (145), javascript... 14559      and (2348), drag (4067), drop (3170), javascript (4177), tutorial...  
tutorial: js_contextmenu                         4203  1588       context menu javascript (256), javascript context menu (790), jav... 9620       context (2432), contextmenu (479), javascript (3526), menu (2745)...  
tutorial: xmlhttprequest                         1953  1140       c# xmlhttprequest (75), xmlhttp timeout (83), xmlhttprequest c# (... 3294       c# (314), timeout (1089), tutorial (135), xmlhttp (172), xmlhttpr...  
tsunami:  bcp_utility_gotchas                    372   26         bcp Invalid Descriptor Index (7), bcp unexpected end of file (5),... 585        bcp (323), file (90), format (58), invalid (56), sql (58)             
tutorial: javascript-grid-tutorial               296   60         css freeze pane (6), freeze pane javascript (8), javascript grid ... 541        freeze (83), grid (131), javascript (234), pane (34), tutorial (59)   
tsunami:  Raptor_-_Programming_with_Flowchart... 254   174        Raptor Flow Charts (7), raptor flowchart (63), RAPTOR flowcharts ... 455        flowchart (92), flowcharts (18), programing (29), programming (87...  
tsunami:  PowerShell                             233   11         powershell escape dollar sign (2), powershell execute external ex... 315        array (18), in (16), powershell (221), string (31), to (29)           
tsunami:  SQL2005_using_LINQ_in_SQLCLR           168   19         Assembly in host store has a different signature than assembly in... 334        assembly (94), clr (42), in (56), linq (54), sql (88)                 
tsunami:  C_Tuple[T1,_T2]                        112   92         C# tuple (53), c# tuple class (5), c# tuples (17), tuple c# (14),... 222        c# (108), class (6), tuple (83), tuple<t1%2c (4), tuples (21)         
tsunami:  C_stdout_and_stderr                    109   78         C# standard out (4), c# STDERR (14), C# stdout (42), stdout c# (1... 223        c# (105), in (7), out (8), stderr (20), stdout (83)                   
tsunami:  C_TreeNode[T]                          95    42         c# tree node generic (3), C# treenode (26), c# treenode children ... 186        add (7), c# (86), node (13), tree (8), treenode (72)                  
tsunami:  Automatic_ER_Diagram_creation          84    40         automatic er diagram (3), diagram of a tsunami (4), ER diagram cr... 158        automatic (19), diagram (48), diagrams (31), er (18), tsunami (42)    
tsunami:  Javascript_text_selection              76    23         javascript detect text select (2), javascript detect text selecti... 181        firefox (12), ie (6), javascript (63), selection (50), text (50)      
tsunami:  Javascript_SetSelection_~_Javascrip... 75    54         javascript set selection (5), javascript setselection (25), javas... 171        javascript (70), selection (23), set (12), setselection (47), tex...  
tsunami:  Javascript_SetSelection                71    52         javascript set selection (10), javascript setselection (33), set ... 151        javascript (68), selection (17), set (16), setselection (46), text (4)
tsunami:  MSSQL_scope_identity()                 36    16         ms sql %40%40scope (1), ms sql scope identity (2), mssql scope id... 88         identity (23), ms (5), mssql (29), scope (26), sql (5)                
thoughts: precision_speech                       17    12         precision of speech (6), precision speech (2), short and precise ... 37         "precision of speech" (2), %3f (1), of (7), precision (13), speec...  
ee5x:     fpga                                   14    11         FPGA notes (4), learning notes FPGA (1), notes on fpga (4), struc... 32         fpga (13), learning (1), notes (12), on (5), teaching (1)             
tsunami:  Get_stack_trace_in_C_~_C_Code_Snipp... 14    9          C# "get stack trace" (2), c# stack trace (2), c# stack trace code... 31         "get stack trace" (2), c# (14), stack (5), stacktrace (6), trace (4)  
webmark:  hi $.net                               12    5          pspad svn ActiveDir search (1), sfv codeproject (1), tutorials on... 6          + (1), asp.net (2), pspad (1), ultragrid (1), vb6 (1)                 
tsunami:  tsunami_Item_Names                     11    11         names for tsunamis (1), names of the stunami (1), tsunami + names... 23         names (11), of (1), the (1), tsunami (9), tsunamis (1)                
html:     codeedit                               10    5          "dockable components" +C# (1), c# dont store undo buffer (1), Cod... 13         c# (3), code (2), format (2), in (4), line (2)                        
tsunami:  SQL_2005_rowcount,_identity,_scope_... 10    5          %40%40rowcount %40%40identity scope (1), @@ROWCOUNT @@IDENTITY (1... 11         %40%40rowcount (2), + (2), identity (2), records (2), scope (3)       
tsunami:  C_LINQ_Select_with_element_and_inde... 9     7          LINQ Element Select (1), linq index select (1), linq select eleme... 29         element (3), index (6), linq (8), select (9), with (3)                
tsunami:  LINQ_SelectMany                        9     8          LINQ Child Select (1), Linq SelectMany (4), linq selectmany strin... 18         linq (9), select (1), selectmany (6), selectmany() (1), selectman...  
tsunami:  javascript_windowonbeforeunload        8     6          javascript confirm dialog onBeforeUnLoad (1), javascript onbefore... 15         javascript (6), onbeforeunload (3), usage (1), window.onbeforeunl...  
tsunami:  Powershell_Write-Host                  8     7          out-file write-host (1), powershell objects in write-host (1), po... 16         external (1), out-file (1), powershell (7), stdin (2), write-host (5) 
tsunami:  ANSI_SQL_nulls                         8     7          ANS nulls (1), ansi null tsunami (1), ANSI NULLS (3), ANSI SQL go... 14         ansi (6), null (1), nulls (5), sql (1), tsunami (1)                   
tsunami:  ASPNET_Authentication_and_Authoriza... 8     6          asp.net add authorization role password to web.config (1), asp.ne... 13         asp.net (4), from (2), the (2), to (3), username (2)                  
ee5x:     upload/examples/basic.mak              7     7          link86 batch (1), link86 errors (1), loc86 (2), loc86 instruction... 9          batch (1), instructions (1), link86 (2), loc86 (3), loc86.exe (2)     
thoughts: roi_problem                            7     7          allintitle%3A Return on investment problem (1), example of invest... 19         example (2), investment (7), of (2), on (1), problem (7)              
tsunami:  ACM_TechNews_Interesting_Articles      7     7          %E2%80%A2http%3A%2F%2Ftechnews.acm.org%2F (4), acm technews naren... 8          %e2%80%a2http%3a%2f%2ftechnews.acm.org%2f (4), acm (1), could (1)...  
tsunami:  Javascript_Observer_Pattern            7     7          javascript + observer pattern (1), javascript observer pattern (4... 23         + (1), in (1), javascript (7), observer (7), pattern (7)              
tsunami:  LaTeX_Notes                            6     6          +"no page breaks" +section +latex (1), flushcenter latex (2), lat... 12         "no page breaks" (1), +latex (1), +section (1), flushcenter (4), ...  
tsunami:  Magic_syntax                           6     5          EBNF of an if statement (1), MACRO CALL MAGIC MEDITECH (1), magic... 9          "unary operator" (1), ebnf (2), magic (3), meditech (2), syntax (1)   
tsunami:  Nerd_Insults                           6     6          nerd insults (6)                                                     12         insults (6), nerd (6)                                                 
tsunami:  SQL_Server_accent_or_case_sensitive... 6     5          sql accent insensitive collation (1), sql server accent (1), sql ... 18         accent (5), insensitive (1), sensitive (2), server (4), sql (6)       
tsunami:  SQL2005_sp_who2_replacement            6     6          sp who2 (2), SP who2 in sql (1), sp-who2 (2), sql who2 (1)           12         in (1), sp (3), sp-who2 (2), sql (2), who2 (4)                        
tsunami:  SQL_2005_change_the_transaction_log... 6     5          MSSQL Log file location (1), sql 2005 log location script (1), sq... 18         2005 (3), location (4), log (5), sql (3), transaction (3)             
tsunami:  Adding_a_foreign_key_constraint        6     6          "management studio" foreign key constraint (1), adding foreign ke... 19         add (2), constraint (3), foreign (6), key (5), t-sql (3)              
tsunami:  C_analyze_tab_delimited_data           6     5          C# line.split tab (1), c# tab delimited (1), c# tab delimited dat... 19         c# (6), data (3), delimited (4), line.split (1), tab (5)              
tsunami:  IRC_cobol_activity                     6     5          +pokenol (1), cobol tsunami (1), irc + digitalspaghetti + #cobol ... 7          + (2), irc.synirc.org (1), luke (1), tsunami (2), zbo (1)             
}}

==SQL
{{
set nocount on
;with Terms as (
    select  uri_stem = case
                when uri_stem = '/time/item.aspx' then 'tsunami:  ' + dbo.RegexMatchValue(uri_query, '(?i)(?<=^name=).*(?=$|&pk=)') 
                when uri_stem like '/tutorial/%'  then 'tutorial: ' + dbo.RegexMatchValue(uri_stem, '(?i)(?<=^/\w+/).*(?=$|.html?|.aspx)') 
                when uri_stem like '/html/%'      then 'html:     ' + dbo.RegexMatchValue(uri_stem, '(?i)(?<=^/\w+/).*(?=$|.html?)') 
                when uri_stem like '/thoughts/%'  then 'thoughts: ' + dbo.RegexMatchValue(uri_stem, '(?i)(?<=^/\w+/).*(?=$|.html?)') 
                when uri_stem like '/webmark/%'   then 'webmark:  ' + isnull(dbo.RegexMatchValue(uri_query, '(?<=^cmd=).*'), '')
                when uri_stem like '/ee5x/%'      then 'ee5x:     ' + dbo.RegexMatchValue(uri_stem, '(?i)(?<=^/\w+/).*(?=$|.html?)') 
                else uri_stem end,
            term = dbo.Extract_Search_Term(log.referrer)            
    from    v_IIS_Typical log
    where   (log.referrer like '%[&?]q=%' or log.referrer like '%yahoo%')
        and client_ip not like '65.55.165.%'
), ByTerm as (
    select  uri_stem,
            term,
            count = count(*),
            row_number = row_number() over (partition by uri_stem order by count(*) desc)
    from    Terms
    group by uri_stem, term 
), ByWord as (
    select  uri_stem,
            word = lower(value),
            count = count(*),
            row_number = row_number() over (partition by uri_stem order by count(*) desc)
    from    Terms
    cross apply dbo.RegexMatch(term, '"[^"]+"|[^" ]+')
    where   term is not null
    group by uri_stem, value
), ByUriStem as (
    select  uri_stem,
            count = count(*)
    from    Terms
    group by uri_stem
), TermAgg as (
    select  uri_stem,
            in_top_N = sum(count), 
            agg = dbo.JoinWithCommaSpace(distinct term + ' (' + cast(count as varchar) + ')')
    from    ByTerm
    where   row_number <= 5
    group by uri_stem
), WordAgg as (
    select  uri_stem,
            in_top_N = sum(count), 
            agg = dbo.JoinWithCommaSpace(distinct word + ' (' + cast(count as varchar) + ')')
    from    ByWord
    where   row_number <= 5
    group by uri_stem
)
select  uri_stem = cast(dbo.RegexReplace(
						case when len(s.uri_stem) > 50 then left(s.uri_stem, 45) + '...' else s.uri_stem end
						, '&.*|\.aspx|\.html?', '') as varchar(50)),
        s.count,
        term_top_N = t.in_top_N,
        term_agg = cast(case when len(t.agg) > 70 then left(t.agg, 65) + '...' else t.agg end as varchar(70)),
        word_top_N = w.in_top_N,
        word_agg = cast(case when len(w.agg) > 70 then left(w.agg, 65) + '...' else w.agg end as varchar(70))
from    ByUriStem s
left join TermAgg t on t.uri_stem = s.uri_stem
left join WordAgg w on w.uri_stem = s.uri_stem
where   s.count > 5
order by s.count desc
}}

some permissive license goes here
contact