Subscribe to this thread
Home - General / All posts - implicit SQL Loop
lionel

995 post(s)
#23-Jan-18 22:10

Hi

i have sql query selecta4 that aggregate area but for only column of name master and for only value "a4" .

Is there a way using only SQL without use explicitly value contain in column master ( a4 b5 c0) to sum area for each of the distinct value in the column master ? ..

regard's

Attachments:
master_slave_v8.map
sum_area_DiSTINCT_value.png


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

tjhb
10,094 post(s)
#23-Jan-18 22:15

Lionel,

That is not easily understood. Could you also give a translation in French?

(Ainsi vous pourriez d'ailleurs ajouter quelques précisions supplémentaires.)

lionel

995 post(s)
#23-Jan-18 22:30

Hi

is there a way in sql to achieve the same result that this algo :

---for each distinct item in master column => a4 b5....

------take all the row that contain this item => slave a4 a1 a2 a3 contain a4

-----------sum the area for all the row => sum slave area = 2553

-----------------write the value in area_sum

regard's

NB i try to post capture screen that i think ll be easier to understand than a lot of text !


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

tjhb
10,094 post(s)
#23-Jan-18 22:37

Better but still not clear.

take all the row[s] that contain this item

... in which column? Is it [slave], as you say, or [slave_list], which is empty except for one row?

sum the area for all the row[s] => sum slave area = 2553

... do you mean 25553?

And how about saying what the data represents, and what your objective is? That would help.

lionel

995 post(s)
#23-Jan-18 22:45

capture screen in Map show many groups of areas that are links to a main area . to know therelation we need to have a look to the table and the 2 column master and slave . for example a1 ( slave) is attach to a4 ( master) . So a4 is also slave and master . I want to know for each group the area value .

I don't want to use slave_list column name since i create the column and the value by hand ( perhaps it should be use for have the result i want )

yes i mean 25553 ( same typo error than *.csv and *.cvs ) ....

a4 is the master because a4 appear in master and slave . same for c0 ...... and slave column have unique value that is not null .

perhaps the question should be How to iterate through a result set ?

if i follow manifold 9 documentation the example is about select item in drawing before apply SQL function. I need a generic way to apply SQL on selected items without use mouse and drawing to select thoses items but only SQL.


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

tjhb
10,094 post(s)
#23-Jan-18 22:50

That is worse. Please describe the data and the objective. (Yes, in words. Feel free to use French and English.)

lionel

995 post(s)
#23-Jan-18 23:14

if i look at this page http://www.manifold.net/doc/radian/sql_example__learning_to_union_areas_in_sql_from_edit_query.htm

the column label "Region" that contain the value bretagne must be selected ( i mean use a mouse and click on table gui ....whatever ) ! .

I want to know if there is a way in SQL to achieve this butr not only for region="bretagne" but for all distinct value locate in Region column .


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

tjhb
10,094 post(s)
#23-Jan-18 22:44

OK I think I am starting to understand, except for the objective (to come, I hope).

Take the example in your picture, where [master] = 'a4'. There are four rows, where [slave] is one of ('a4', 'a1', 'a2', 'a3').

It is easy to sum the value of [area] for that group. It is easy to put that sum into [area_sum] for each of those rows. But is that what you want? Is it useful to write the same [area_sum] for each member of the group, or do you want something else?

And I am still confused by this:

Is there a way using only SQL without use explicitly value contain in column master ( a4 b5 c0) to sum area for each of the distinct value in the column master ? ..

lionel

995 post(s)
#23-Jan-18 22:59

Hi

best ll be that the area value of each group appear only in the row that contain master item ( master value = slave value)

My aim is also to understand better SQL logic compare to script .

so i need to better understand structure of data ( table only in SQL but with matrix/Vector in manifold ) and loop ( while for each) but in SQL context ....perhaps i write something that don't make sense ....

for understand SQL , i try first to avoid to use Geometry type column because , we need to understand branch , hole , point , area , line ......concept before understand geometry function .

thank's


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

tjhb
10,094 post(s)
#23-Jan-18 23:26

OK.

Well, the data structure is not particularly good, but FWIW try:

UPDATE

    (

    SELECT

        [V].[ID][V].[area][V].[area_sum],

        [W].[slave area]

    FROM

        [test] AS [V]

        LEFT JOIN

        (

        SELECT

            [T].[ID],

            SUM([U].[area]AS [slave area]

        FROM

            (

            SELECT *

            FROM [test]

            WHERE [master] = [slave] -- target row

            ) AS [T]

            LEFT JOIN

            [test] AS [U]

            ON [T].[ID] <> [U].[ID] -- other rows...

            AND [T].[master] = [U].[master] -- ...for same master

        GROUP BY [T].[ID]

        ) AS [W]

    ON [V].[ID] = [W].[ID]

    )

SET [area_sum] = [area] + Coalesce([slave area], 0)

;

I think that's what you want, but I don't think it will help you much yet.

(If you can see why it does what it does, then that is fantastic and I am wrong.)

I think what will help most for now, is to spend several weeks reading a few pages of Chris Fehily every day. Each day, review some pages that you have already covered, then read some new pages.

Simple! Make written notes on the printed page as you go. (Avoid ebooks for SQL.)

Notice that there is absolutely nothing here to do with iteration or looping. That was a red herring (un cheveux sur la soupe?). Pure SQL does not have either iteration or loops. It has sets.

tjhb
10,094 post(s)
#23-Jan-18 23:42

I updated the code above and make a mistake. Better as it was:

UPDATE

    (

    SELECT

        [V].[ID][V].[area][V].[area_sum],

        [W].[slave area]

    FROM

        [test] AS [V]

        INNER JOIN

        (

        SELECT

            [T].[ID],

            SUM([U].[area]AS [slave area]

        FROM

            (

            SELECT *

            FROM [test]

            WHERE [master] = [slave] -- target row

            ) AS [T]

            LEFT JOIN

            [test] AS [U]

            ON [T].[ID] <> [U].[ID] -- other rows...

            AND [T].[master] = [U].[master] -- ...for same master

        GROUP BY [T].[ID]

        ) AS [W]

    ON [V].[ID] = [W].[ID]

    )

SET [area_sum] = [area] + [slave area]

;

lionel

995 post(s)
#24-Jan-18 00:46

Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

lionel

995 post(s)
#23-Jan-18 23:46

thank's a lot

yes i want to understand why it does what it does .


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

tjhb
10,094 post(s)
#24-Jan-18 00:40

Good.

If you want I can take you through it in pieces. (There are about 6 pieces.)

But only if you have ordered a physical copy of Fehily!

If you're doing it on your own, the first important thing is to start from the inside, not from the top.

So here, you start from

(

SELECT *

FROM [test]

WHERE ...

)

You can run that bit by itself.

lionel

995 post(s)
#24-Jan-18 00:48

Attachments:
sql_select.png


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

tjhb
10,094 post(s)
#24-Jan-18 00:55

That is truly excellent. You would be a good SQL teacher!

One obvious thing to do, if you haven't already: comment out the first and last lines (UPDATE... and SET...) and run the remainder, to see the table that will be updated, listing target rows alongside source values.

We should always do that before running an UPDATE query, but it also helps understand it.

lionel

995 post(s)
#24-Jan-18 01:38

ok a virtual variable ( column name ) has been create of name slave_area in table W that contain table U where call to function sum occur .

I need read carefully the SQL to understand why slave_area contain the sum of all values except where master= slave for each master item !!

Attachments:
sql_select.png


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

tjhb
10,094 post(s)
#24-Jan-18 02:15

That is exactly the right question. To answer it...

This alternative query has exactly the same logic:

UPDATE

    (

    SELECT

        [V].[ID][V].[area][V].[area_sum],

        [W].[slave area]

    FROM

        [test] AS [V]

        INNER JOIN

        (

        SELECT

            [T].[ID],

            SUM([U].[area]AS [slave area]

        FROM

            (

            SELECT *

            FROM [test]

            WHERE [master] = [slave] -- target row

            ) AS [T]

            LEFT JOIN

            (

            SELECT *

            FROM [test]

            WHERE [master] <> [slave] -- other row

            ) AS [U]

            ON [T].[master] = [U].[master]

        GROUP BY [T].[ID]

        ) AS [W]

    ON [V].[ID] = [W].[ID]

    )

SET [area_sum] = [area] + [slave area]

;

Is that more obvious?

So which version is better and why?

The first version will be faster, for two reasons, taken together:

(1) Because the columns [master] and [slave] are not indexed. A single pass through the table comparing the strings in those columns is better than two searches (although we can expect the second search to use cache).

(2) Because in Manifold 8 the built-in ID column is indexed. Replacing a second comparison of unindexed string values with a BTREE comparison of IDs is potentially* a significant saving. (*It always pays to test, rather than assume.)

Here, the first query lists the IDs of all rows in the set [master] = [slave] (into virtual table T). So we know that any row whose ID is not on that list (not in T) is a member of the complementary set [master] <> [slave] (virtual table U). They are disjoint sets, just as in the second query.

(This excludes the matching on [master], which is common to the logic of both queries.)

lionel

995 post(s)
#25-Jan-18 01:43

here a doc for relation 1 to-> 1 ( basic not 1 to-> n and nothing about RIGHT LEFT ) . So to simplify SQL is a way to apply query on tables by duplicate thoses tables to create relations beetween themselves with each time some filters for each new relation .

So to understand SQL using text fields you need to know the structure and content in each table to know howto manage rows that don't have relation with other table ( mean null value) .

NB1 at this time even don't search a lot ; i don't find on internet better documentation than manifold 9 documentation about index ( choice of btree rtree ..... in relation with write/ read ) . Wikipedia is in all cases a good start to structure the knowledge about any subjects . The question should be wich type of index manifold use in each different context ? ( ..TODO )

NB2 is there SQL article / book that help learn SQL without use SQL reserved words but only focus on logic ( algo and data ) .

https://en.wikipedia.org/wiki/Venn_diagram

https://fr.slideshare.net/ifourtechnolabindia/sql-joins-using-venn-diagrams

regard's

Attachments:
SQL_join.png


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

tjhb
10,094 post(s)
#25-Jan-18 03:48

Lionel,

For my part I have the same language problems as before.

However, clearly wrong statements do stand out. The more false the better!

Here is one:

"UnionAll " [in] manifold gis is call[ed] "FULL OUTER JOIN" in SQL

That is utterly and completely false. Get it out of your head!

There is no link whatsoever between an aggregate function and a join.

Read Chris Fehily. And if you like, report on progress.

lionel

995 post(s)
#25-Jan-18 18:44

I know, it's just an image. If I had to be precise it would take me too much time.

Je sais , ce n'est qu'une image . Si je devais être précis cela me prendrais trop de temps à écrire.

the 2 circles make me think of photoshop combine tool !!!


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

lionel

995 post(s)
#25-Jan-18 02:47

here logic in a gui way using venn diagram ( update of JOIN capture screen ) .

"UnionAll " is manifold gis is call "FULL OUTER JOIN" in SQL !!

*v8.map that contain v9 sql code ll be post ...(TODO)

all other JOIN types can be derived from CROSS JOIN !!

Attachments:
SQL_join_v2.png


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

lionel

995 post(s)
#25-Jan-18 04:53

Some comment

a) "null" is locate/use in

-SQL for Advisor_ID or Students_ID in SQL for IS NULL

-table column Advisor_Name , Student_Name or any cells of row that don't match ll contain <NULL> in v9 and empty in V8

b) import table from v8 inside V9

add a new column mfd_id of type int64 and don't use id v8 of type integer 32 bit unsigned size 4 that is translate to uint32

c) by default select must have explicit column for speed

d) for test i work on table attach to drawing each row is a point geometry

e) sql query script code v9 can be slide to QueryBuilder like table and drawing !!

regard's

Attachments:
left_outer_join.png
loop_v8.map


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

tjhb
10,094 post(s)
#25-Jan-18 05:44

Lionel,

Please do this reading, of course, but please keep your reading notes to yourself? They won’t work for everyone.

On the other hand:

What software do you use for annotating screenshots? You are very good at this, I would like to try the same thing, I think the forum needs more of it especially for 9.

Tim

tjhb
10,094 post(s)
#25-Jan-18 07:53

Actually Lionel, you are really good at these conceptual pictures for SQL.

Hmmn, what can we do with this??

lionel

995 post(s)
#25-Jan-18 18:47

i do it for manifold beginner and because i like to understand things in a easy way .

All the people in the forum are professionnal ..... I m not a gis professionnal ...

I have a lot of reading notes ( web design ) so OK i ll stop post them ...

All images have "error" so have to be update ... all the time

I could use adobe CS6 suite ( fireworks , photoshop ...) and wacom tablet but my tools that i use its simplest .

I use only some basics tools :

--a capture screen : press on key "imp screen" on windows 10 let me define the area to capture

--paint.NET i master a lot so easy to mix raster image and text

--abby screenshot reader to convert text image to text glyph .

A picture is worth a thousand words


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

lionel

995 post(s)
#25-Jan-18 19:14

the name of tool for capture screen (never appear when use the tool) can be download at https://app.prntscr.com/en/download.html

I don't understand how they can do business with a such amazing free tool !!!

Ultrasnap is also a really usefull tool http://www.mediachance.com/ultrasnap/index.html

the guy behind all mediachance software is a genious .... ( when coding)


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

lionel

995 post(s)
#25-Jan-18 03:28

Attachments:
SQL_join_v3.png


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

lionel

995 post(s)
#27-Jan-18 21:48

this thread could be name join statement because combine same table can be achieve using aliase combine with filter ( sub condition) is the paradigm of SQL . Join is cover in the Chris Fehily book and mfd9 documentation .

For documentation relative to SQL , It ll be nice to have a link to a map file that already contain all the table that the article cover . In a way it could be like call mfd9_doc_*.map like there is book*.map for Fehily .


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

lionel

995 post(s)
#30-Nov-18 22:40

not really easy to unsderstand code . Is there book that cover SQL code example refer to specific problem like tjhb write for many problems ask in the forum .

new way to visually understand SQL code but not really helpfull to write it

So here we create 3 virtual table of name test using SQL reserved word AS

Attachments:
implicitloop_coltable_relation.png


Book about Science , cosmological model , Interstellar travels

Boyle surface fr ,en

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.