Differences

This shows you the differences between two versions of the page.

--- admin:indexing:activecounts:sql [2011/01/21 04:46]
els created
+++ admin:indexing:activecounts:sql [2016/06/28 22:38] (current)
@@ Line 1: / Line 1: @@
+~~NOTOC~~
 {{page>:top_add&nofooter&noeditbtn}}
@@ Line 9: / Line 10: @@
 **[[admin:indexing:activecounts:sql|SQL]]** |
 [[admin:indexing:activecounts:optimization|Optimization]] |
-[[admin:indexing:activecounts:tips|Tips]] |
+[[admin:indexing:activecounts:tips|Tips]]
 ----
+==== The SQL Behind ActiveCounts ====
+The example on the previous page demonstrated ActiveCounts in a web page; however, ActiveCounts is not a set of web controls.  ActiveCounts is really just a powerful set of SQL statements that are highly optimized using the Omnidex indexes.  This means that ActiveCounts can be implemented in any application that calls SQL.
-==== The SQL Behind ActiveCounts ====
+ActiveCounts is accomplished using SQL statements that request a set of values and their counts based on the criteria so far.  The following basic SQL statements supply the counts needed for the example on the previous page.
+First, the user requested to see the states and their counts:
+<code>
+  select        STATE, count(*)
+    from        LIST
+    group by    STATE;
+</code>
+The user selected the states in the Western United States, and then requested to see the Genders and their counts:
+<code>
+  select        GENDER, count(*)
+    from        LIST
+    where       STATE in ('AZ','CA','CO','NM','OR','UT','WA')
+    group by    GENDER;
+</code>
+The user selected the Women in the Western United States, and then requested to see the Educations and their counts:
+<code>
+  select        EDUCATION, count(*)
+    from        LIST
+    where       STATE in ('AZ','CA','CO','NM','OR','UT','WA') and
+                GENDER = 'F'
+    group by    EDUCATION;
+</code>
+The user selected those people with graduate degrees, and may have continued narrowing his search.
+As you can see, these SQL statements simple request counts that are filtered by the criteria entered so far, grouped by the next value they want to see.  The simplicity of this approach allows it to be more easily integrated into an application.
+==== More Complex SQL ====
+The actual SQL statements for the example on the previous page was slightly more complex, only in that it references dimension tables to provide descriptions rather than codes.  The actual SQL statements are as follows:
+First, the presentation of the States:
+<code>
+  select        STATES.DESCRIPTION,
+                LIST.STATE,
+                count(*)
+    from        LIST
+    join        STATES on LIST.STATE = STATES.STATE
+    group by    STATES.DESCRIPTION,
+                LIST.STATE;
+</code>
+Notice that the DESCRIPTION column was retrieved for presentation, and the STATE column was retrieved for inclusion in future criteria.
+Second, the presentation of the Genders:
+<code>
+  select        GENDERS.DESCRIPTION,
+                LIST.GENDER,
+                count(*)
+    from        LIST
+    join        GENDERS on LIST.GENDER = GENDERS.GENDER
+    where       LIST.STATE in ('AZ', 'CA', 'CO', 'NM', 'OR', 'UT', 'WA')
+    group by    GENDERS.DESCRIPTION,
+                LIST.GENDER;
+</code>
+Third, the presentation of the Educations:
+<code>
+  select        $ifnull(EDUCATION.DESCRIPTION, case LIST.EDUCATION
+                  when ' ' then 'Did not specify'
+                  else LIST.EDUCATION
+                end),
+                LIST.EDUCATION,
+                count(*)
+    from        LIST
+    left join   EDUCATION on LIST.EDUCATION = EDUCATION.EDUCATION
+    where       LIST.GENDER = 'F' and
+                LIST.STATE in ('AZ', 'CA', 'CO', 'NM', 'OR', 'UT', 'WA')
+    group by    EDUCATION.DESCRIPTION,
+                LIST.EDUCATION
+    order by    LIST.EDUCATION;
+</code>
+Notice that this SQL statement is more complex, involving $IFNULL functions and Left Outer Joins.  These were beneficial because not every row in the LIST table had a value in the EDUCATION column.  This SQL would be the standard approach for obtaining the descriptions for those entries that did have a value.  This example shows that the SQL can be more complex and still be used in ActiveCounts.
+All of these SQL statements are straightforward, traditional SQL.  The secret to ActiveCounts is the optimization of the SQL statements.  Specialized optimization that uses the Omnidex indexing allows these queries to be fast, even on very large databases.  Continue on to read about this optimization.

Back to top

admin/indexing/activecounts/sql.1295585209.txt.gz · Last modified: 2016/06/28 22:38 (external edit)

Sitemap Recent Changes