OrenBochman

<table class="generaltable">
<thead>
<tr>
<th class="header c0" style="text-align:left;" scope="col">Field name</th>
<th class="header c1" style="text-align:left;" scope="col">Field type</th>
<th class="header c2" style="text-align:left;" scope="col">Field description</th>
<th class="header c3 lastcol" style="text-align:center;" scope="col">Action</th>
</tr>
</thead>
<tbody><tr class="r0">

OrenBochman

these are some sql queries to do analytics on the mediawiki database.

OrenBochman

CatScan modes:
cs  -- pages by category
ts  -- pages by template
al  -- all pages
ss  -- stubs := (having_less_than_x_bytes_1 || less_than_x_links_1 ) in mainspace
rc  -- recent changes
iul -- all images
cl  -- all categories

OrenBochman

CREATE TABLE subjects(
    id INT(11) NOT NULL AUTO_INCREMENT,
    menu_name VARCHAR(30) NOT NULL,
    position INT(3) NOT NULL,
    visible TINTINT(1) NOT NULL,
    PRIMERY KEY(id)
);

CREATE TABLE pages(
    id INT(11) NOT NULL AUTO_INCREMENT,

OrenBochman

SELECT log_page, log_type, log_action, log_timestamp, log_user,user_id,user_name,log_timestamp,rev_timestamp,rev_page
  FROM logging
  JOIN user ON log_user = user_id
  JOIN revision_userindex ON  rev_page = log_page
 WHERE log_type = "patrol"
   AND log_action = "patrol"
   AND user_name NOT LIKE '%bot'            -- to filter robots
 GROUP BY log_page
 ORDER BY log_timestamp DESC                -- to get the last actions
 LIMIT 50;                                  -- to reduce load

OrenBochman

SELECT rev_page   
      FROM revision_userindex   
     INNER JOIN page ON page.page_id = rev_page  
     WHERE rev_user_text = "Jimbo Wales" 
       AND page_namespace IN (0,1)    
     GROUP BY rev_page 
    HAVING count(rev_page) > 20
     ORDER BY COUNT(*) DESC

OrenBochman

SELECT CAST(parentrevision.rev_len AS SIGNED) - CAST(revision.rev_len AS SIGNED) AS diff FROM revision JOIN revision AS parentrevision ON parentrevision.rev_id = revision.rev_parent_id WHERE revision.rev_parent_id <> 0 ORDER BY revision.rev_id DESC LIMIT 1;

OrenBochman

My first examples with [***knitr***](http://yihui.name/knitr/)
-----------------------------------------
Let's include some simple R code:
```{r}
1+2
```
That worked.

Let's include a plot:
```{r fig.width=4, fig.height=4}

OrenBochman

dat <- data.frame(party=c("CDU", "FDP", "CSU", "SPD",
                          "The Left", "The Greens"),
                  members.of.parliament=c(193, 93, 44, 
                                          146, 76, 68))
library(googleVis)
## Doughnut chart - a pie with a hole
doughnut <- gvisPieChart(dat, 
                      options=list(
                        width=500,
                        height=500,

OrenBochman

dat <- data.frame(Product=c("Milk", "Butter", "Yoghurt", "<b>Sum</b>"),
                  Revenue=c(2230, 43908, 231, 46369))
dat <- within(dat, {'% of Revenue' <- c(Revenue / Revenue[4])})

library(googleVis)
tbl <- gvisTable(dat, options=list(width=300, height=150),
                 formats=list(Revenue="#,###", '% of Revenue'='#.#%'),
                 chartid="formattedtable")
plot(tbl)