Groups > comp.os.linux.advocacy > #373505

Re: Geographic breakdown of cola regs

From	DFS <nospam@dfs.com>
Newsgroups	comp.os.linux.advocacy
Subject	Re: Geographic breakdown of cola regs
Date	2016-09-29 19:51 -0400
Organization	A noiseless patient Spider
Message-ID	<nsk9d8$3ec$3@dont-email.me> (permalink)
References	(5 earlier) <nse22f$hh7$2@dont-email.me> <dc73fe3e-0c44-445d-9b1c-0c3e6ff9b4ae@googlegroups.com> <e44eb1f5-d9f2-43a8-b758-5c54512514b0@googlegroups.com> <nsee28$1ii$1@dont-email.me> <acbb73a7-9c4e-4826-b5b3-1a0ab61380f0@googlegroups.com>

Show all headers | View raw

On 9/27/2016 3:24 PM, Steve Carroll wrote:

> Excellent stuff! Seriously, thanks again for this!

You're welcome.

To understand the data and how best to store it, I played around with a 
few designs in Access (populated via python and pyodbc) before 
committing to the final SQLite structure I have now (the DDL to which I 
posted the other day).

One simple design I tried was this:  http://imgur.com/a/rmIL9

It stores the data essentially as it looks onscreen.  It's easy to see 
the entire contents of a post, but not recommended whatsoever: it will 
grow huge, contain a lot of repeating data, and in general is slow and 
difficult to query against (look at that and try to imagine the SQL to 
count how many times chrisv said *plonk*, or how to count the number of 
posts everyone made in Sep 2015 - when you have 100K posts such queries 
probably won't ever finish).

Another "master" table design I tried was: http://imgur.com/a/qDjPj

I populated it with about 5K rows, to see what data was frequently 
captured in eternal-september.  After reviewing the data, I decided to 
not store Organization, Complaints-To, the various 'X-' fields, the 
NNTP-Posting-Host, and some others.  You could use a table like this 
(need to add server and group columns), plus a separate table for the 
post body, and that would be a workable design that's easy to query and 
populate. Cons are repeating data, and difficulty in accounting for 
nymshifts.

> I've been using this
> stupid pokemon data and it's OK considering I'm working with REST
> API concepts... but I really wanted to get into some of this stuff you
> and Sandman are doing with SQL databases. The most advanced
> queries I've run to date were simple joins, ex:
>
> SELECT `ID`,`post_title` AS `Product Description`,
>  `price`.`meta_value` AS `Price`, `stock`.`meta_value` AS `Stock`
> FROM `wp_posts`
> LEFT JOIN `wp_postmeta` AS `price` ON wp_posts.ID = price.post_id
> AND price.meta_key = 'regular_price'
> LEFT JOIN `wp_postmeta` AS `stock` ON wp_posts.ID = stock.post_id
> AND stock.meta_key = 'stock'
> WHERE (wp_posts.ID > '0') AND post_type = "product" ORDER BY `post_title`
>
>
> It's probably lame but it did the job.

Standard left join.  Nothing lame there, except it refers to 'Pokemon'. 
What kind of data is that?

Thread

Geographic breakdown of cola regs DFS <nospam@dfs.com> - 2016-09-26 12:57 -0400
  Re: Geographic breakdown of cola regs Steve Carroll <fretwizzer@gmail.com> - 2016-09-26 10:14 -0700
    Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-26 13:38 -0400
      Re: Geographic breakdown of cola regs Steve Carroll <fretwizzer@gmail.com> - 2016-09-26 10:48 -0700
        Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-26 13:56 -0400
          Re: Geographic breakdown of cola regs Steve Carroll <fretwizzer@gmail.com> - 2016-09-26 11:09 -0700
            Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-26 14:13 -0400
              Re: Geographic breakdown of cola regs "Octavian W. Lagrange" <olagrang@perch.invalid> - 2016-09-26 18:30 +0000
                Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-26 16:51 -0400
              Re: Geographic breakdown of cola regs "Octavian W. Lagrange" <olagrang@perch.invalid> - 2016-09-27 19:06 +0000
                Re: Geographic breakdown of cola regs Snit <usenet@gallopinginsanity.com> - 2016-09-27 12:32 -0700
                Re: Geographic breakdown of cola regs "Octavian W. Lagrange" <olagrang@perch.invalid> - 2016-09-27 19:50 +0000
                Re: Geographic breakdown of cola regs Snit <usenet@gallopinginsanity.com> - 2016-09-27 13:19 -0700
                Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-27 20:12 -0400
                Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-28 08:01 -0400
                Re: Geographic breakdown of cola regs Jim Polaski <jpolaski@linuxmail.org> - 2016-09-28 14:18 +0000
                Re: Geographic breakdown of cola regs Snit <usenet@gallopinginsanity.com> - 2016-09-28 08:46 -0700
                Re: Geographic breakdown of cola regs Steve Carroll <fretwizzer@gmail.com> - 2016-09-28 10:00 -0700
                Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-28 14:55 -0400
      Re: Geographic breakdown of cola regs Marek Novotny <marek.novotny@marspolar.com> - 2016-09-26 19:06 +0000
        Re: Geographic breakdown of cola regs chrisv <chrisv@nospam.invalid> - 2016-09-26 14:12 -0500
          Re: Geographic breakdown of cola regs GreyCloud <Cumulus@mist.com> - 2016-09-26 13:53 -0600
            Re: Geographic breakdown of cola regs DFS <nospam@dfs.com> - 2016-09-26 19:03 -0400
              Re: Geographic breakdown of cola regs GreyCloud <Cumulus@mist.com> - 2016-09-26 18:32 -0600
            Re: Geographic breakdown of cola regs -hh <recscuba_google@huntzinger.com> - 2016-09-27 15:35 -0700
              Re: Geographic breakdown of cola regs GreyCloud <Cumulus@mist.com> - 2016-09-27 16:45 -0600
          Re: Geographic breakdown of cola regs William Poaster <wp@dev.null> - 2016-09-26 22:35 +0100
            Re: Geographic breakdown of cola regs Chris Ahlstrom <OFeem1987@teleworm.us> - 2016-09-26 17:55 -0400
            Re: Geographic breakdown of cola regs DFS <nospam@dfs.com> - 2016-09-26 19:04 -0400
        Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-26 16:55 -0400
          Re: Geographic breakdown of cola regs Snit <usenet@gallopinginsanity.com> - 2016-09-26 14:08 -0700
            Re: Geographic breakdown of cola regs Peter Köhlmann <peter-koehlmann@t-online.de> - 2016-09-27 00:19 +0200
              Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-26 18:38 -0400
              Re: Geographic breakdown of cola regs Snit <usenet@gallopinginsanity.com> - 2016-09-26 15:27 -0700
    Re: Geographic breakdown of cola regs DFS <nospam@dfs.com> - 2016-09-26 19:18 -0400
      Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-26 19:22 -0400
      Re: Geographic breakdown of cola regs Snit <usenet@gallopinginsanity.com> - 2016-09-26 16:36 -0700
        Re: Geographic breakdown of cola regs Steve Carroll <fretwizzer@gmail.com> - 2016-09-26 17:54 -0700
          Re: Geographic breakdown of cola regs DFS <nospam@dfs.com> - 2016-09-27 11:09 -0400
            Re: Geographic breakdown of cola regs Steve Carroll <fretwizzer@gmail.com> - 2016-09-27 08:58 -0700
              Re: Geographic breakdown of cola regs Steve Carroll <fretwizzer@gmail.com> - 2016-09-27 09:50 -0700
                Re: Geographic breakdown of cola regs DFS <nospam@dfs.com> - 2016-09-27 14:33 -0400
                Re: Geographic breakdown of cola regs Steve Carroll <fretwizzer@gmail.com> - 2016-09-27 12:24 -0700
                Re: Geographic breakdown of cola regs DFS <nospam@dfs.com> - 2016-09-29 19:51 -0400
                Re: Geographic breakdown of cola regs Steve Carroll <fretwizzer@gmail.com> - 2016-09-29 17:22 -0700
                Re: Geographic breakdown of cola regs DFS <nospam@dfs.com> - 2016-09-29 23:27 -0400
      Re: Geographic breakdown of cola regs GreyCloud <Cumulus@mist.com> - 2016-09-26 18:35 -0600
        Re: Geographic breakdown of cola regs "Octavian W. Lagrange" <olagrang@perch.invalid> - 2016-09-27 05:38 +0000
        Re: Geographic breakdown of cola regs DFS <nospam@dfs.com> - 2016-09-27 01:48 -0400
          Re: Geographic breakdown of cola regs GreyCloud <Cumulus@mist.com> - 2016-09-27 12:08 -0600
        Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-27 08:48 -0400
          Re: Geographic breakdown of cola regs GreyCloud <Cumulus@mist.com> - 2016-09-27 12:10 -0600
            Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-27 20:09 -0400
      Re: Geographic breakdown of cola regs "Octavian W. Lagrange" <olagrang@perch.invalid> - 2016-09-27 05:57 +0000
        Re: Geographic breakdown of cola regs DFS <nospam@dfs.com> - 2016-09-27 11:10 -0400
          Re: Geographic breakdown of cola regs "Octavian W. Lagrange" <olagrang@perch.invalid> - 2016-09-27 15:46 +0000
      Re: Geographic breakdown of cola regs Incubus <incubus9536612@gmail.com> - 2016-09-27 07:54 -0700
        Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-27 11:18 -0400
          Re: Geographic breakdown of cola regs DFS <nospam@dfs.com> - 2016-09-27 11:28 -0400
  Re: Geographic breakdown of cola regs fr314159@gmail.com - 2016-09-26 10:23 -0700
  Re: Geographic breakdown of cola regs Sandman <mr@sandman.net> - 2016-09-27 09:06 +0000
    Re: Geographic breakdown of cola regs GreyCloud <Cumulus@mist.com> - 2016-09-27 12:11 -0600
      Re: Geographic breakdown of cola regs Peter Köhlmann <peter-koehlmann@t-online.de> - 2016-09-27 20:28 +0200
        Re: Geographic breakdown of cola regs GreyCloud <Cumulus@mist.com> - 2016-09-27 15:04 -0600
        Re: Geographic breakdown of cola regs Chris Ahlstrom <OFeem1987@teleworm.us> - 2016-09-27 19:00 -0400
      Re: Geographic breakdown of cola regs Sandman <mr@sandman.net> - 2016-09-27 20:32 +0000
        Re: Geographic breakdown of cola regs Snit <usenet@gallopinginsanity.com> - 2016-09-27 13:34 -0700

csiph-web