Path: csiph.com!usenet.pasdenom.info!dedibox.gegeweb.org!gegeweb.eu!nntpfeed.proxad.net!proxad.net!feeder1-2.proxad.net!usenet-fr.net!nerim.net!novso.com!newsfeed.xs4all.nl!newsfeed3.news.xs4all.nl!xs4all!post.news.xs4all.nl!not-for-mail
To: python-list@python.org
From: Dennis Lee Bieber <wlfraed@ix.netcom.com>
Subject: Re: efficient way to process data
Date: Sun, 12 Jan 2014 17:43:46 -0500
Organization: IISS Elusive Unicorn
References: <CACwCsY6KBDVkS5jCMh9GyvhHyVgqcAH3YAYnGpMQvfBwexaTcw@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.5389.1389566640.18130.python-list@python.org>
Lines: 54
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:63804

On Sun, 12 Jan 2014 14:23:17 -0500, Larry Martell <larry.martell@gmail.com>
declaimed the following:

>I have an python app that queries a MySQL DB. The query has this form:
>
>SELECT a, b, c, d, AVG(e), STD(e), CONCAT(x, ',', y) as f
>FROM t
>GROUP BY a, b, c, d, f
>
>x and y are numbers (378.18, 2213.797 or 378.218, 2213.949 or
>10053.490, 2542.094).
>

	Decimal (Numeric) or floating/real. If the latter, the internal storage
may not be exact (378.1811111111 and 378.179999999 may both "display" as
378.18, but will not match for grouping).

>The business issue is that if either x or y in 2 rows that are in the
>same a, b, c, d group are within 1 of each other then they should be
>grouped together. And to make it more complicated, the tolerance is
>applied as a rolling continuum. For example, if the x and y in a set
>of grouped rows are:
>
	As I understand group by, it will first group by "a", WITHIN the "a"
groups it will then group by "b"... Probably not a matter germane to the
problem as you are concerning yourself with the STRING representation of
"x" and "y" with a comma delimiter -- which is only looked at if the
"a,b,c,d" are equal... Thing is, a string comparison is going to operate
strictly left to right -- it won't even see your "y" value unless all the
"x" value is equal.

	You may need to operate using subselects... So that you can specify
something like

	where	abs(s1.x -s2.x) < tolerance or abs(s1.y-s2.y) < tolerance
		and (s1.a = s2.a ... s1.d = s2.d)

s1/s1 are the subselects (you may need a primary key <> primary key to
avoid having it output a record where the two subselects are for the SAME
record -- or maybe not, since you /do/ want that record also output). Going
to be a costly query since you are basically doing

	foreach r1 in s1
		foreach r2 in s2
			emit r2 when...





-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
    wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/