Statistics With Tcl And Mysql

Question

I have a Mysql database of doorways.

We are a small company and unfortunately I am not good at programming, I would be happy if someone could help me. We have security access with a badge at every door. I need statistics that show which name and how many doors he has entered

this is what the Mysql database looks like:

ID  key         ctime       name    floor   floorid door
1   114554737   1609613062  work1   1       D1      112
2   114554737   1609662335  work1   1       D1      112
3   114554737   1609662388  work1   1       D1      115
4   114554737   1609665480  work1   2       D1      201
5   114554738   1609701179  work2   2       D1      202
6   114554738   1609701188  work2   1       D1      101
7   114554738   1609701195  work2   2       D1      225
8   114554738   1609701253  work2   3       D1      318
9   114554738   1609707953  work2   4       D1      412
10  114554738   1609876824  work2   5       D1      500
11  114554739   1609956064  work3   1       D1      100
12  114554739   1609956067  work3   1       D1      101
13  114554739   1610084925  work3   1       D1      100
14  114554739   1610084928  work3   1       D1      100
15  114554740   1610141106  work4   2       D1      201
16  114554740   1610141109  work4   2       D1      202
17  114554740   1610177322  work4   2       D1      202
18  114554740   1610178412  work4   2       D1      202
19  114554740   1610207104  work4   2       D1      202
20  114554741   1610216851  work5   2       D1      202
21  114554741   1610268582  work5   2       D1      202
22  114554741   1610268908  work5   2       D1      202
23  114554741   1610271923  work5   2       D1      206
24  114554741   1610275117  work5   2       D1      206
25  114554741   1610293137  work5   3       D1      301

I am currently using this script

that shows me which name entered which door most often

package require mysqltcl
load /usr/lib/tcltk/x86_64-linux-gnu/mysqltcl-3.052/libmysqltcl3.052.so

set db_handle [mysqlconnect -host localhost -port 3306 -user xxx -pass xx -db doors]


bind pub -|- !ustats stats:ustats


proc stats:ustats { nick uhost hand chan text } {

    global db_handle

    set maxresults 50

    set sql "SELECT count(name) as namecount, key, ctime, name, floor, floorid, door from stats GROUP by name ORDER BY nickcount DESC"

    set result [mysqlsel $db_handle $sql -list];

    if {$result > 0} {
    set counter 1

    putnow "PRIVMSG #test :\00315$$\00314!\00315$$-$$\00314Top Query Stats\00315$$"

    for {set i 0} {$i < $maxresults} {incr i} {

    set record [lindex $result $i]
    set count [lindex $record 0] 
    set key [lindex $record 1]
    set ctime [lindex $record 2]
    set name [lindex $record 3]
    set floor [lindex $record 4]
    set floorid [lindex $record 5]
    set door [lindex $record 6]


    if {$count != ""} {

    putquick "PRIVMSG #chan :$counter $count $name $door $floor $floorid"
    incr counter
    }
    }
    } 
    mysqlendquery $db_handle
}

I need an output that not only shows me which name has opened the most doors, but also which doors and how many openings and most doors from the left the largest to the right the smallest

example:

work2 opened 6x total, 1x door 202, 1x door 101, 1x door 225, 1x door 318, 1x door 412, 1x door 500
work5 opened 6x total, 3x door 202, 2x door 206, 1x door 301
work4 opened 5x total, 4x door 202, 1x door 201
work1 opened 4x total, 2x door 112, 1x door 115, 1x door 201
work3 opened 4x total, 3x door 100, 1x door 101

you would help me extremely, thank you very much

Regards

user_205608 · Answer

What you need here is a slightly elaborate SQL query. It will be made up of subqueries.

The first one is this (fiddle). It gets you the total number of door-open items for each name.

 SELECT COUNT(*) total, name FROM stats GROUP BY name

The next one is this (fiddle). It gets you the number of opens per door and name.

SELECT COUNT(*) bydoor, name, door FROM stats GROUP BY name, door

The third one (fiddle) incorporates the second one, and gets you one line for each name, showing the door-opens.

SELECT name, 
       GROUP_CONCAT(CONCAT(bydoor,'X',' door ', door) ORDER BY bydoor DESC) details
  FROM (   SELECT COUNT(*) bydoor,
                  name,
                  door
             FROM stats
            GROUP BY name, door
        ) s
  GROUP BY name

Finally, you need a JOIN to tie those subqueries together, of this form.

   SELECT t.name, t.total, d.details
     FROM ( .... the first subquery ....) t
     JOIN ( .... the second subquery .... ) d ON t.name = d.name
    ORDER BY t.total DESC, t.name

All spelled out it looks like this (fiddle). You put it into your sql variable and you're good to go. It's just a multiline string.

set sql {SELECT t.name, t.total, d.details
  FROM (SELECT COUNT(*) total, name FROM stats GROUP BY name) t
  JOIN (  SELECT name, 
                 GROUP_CONCAT(CONCAT(bydoor,'X',' door ', door) ORDER BY bydoor DESC) details
            FROM (   SELECT COUNT(*) bydoor,
                            name,
                            door
                       FROM stats
                      GROUP BY name, door
                 ) s
           GROUP BY name
        ) d ON t.name = d.name
  ORDER BY t.total DESC, t.name}

So there's a query made of a bunch of nested queries.

There are some tricks here for you to learn as you get better at SQL data analysis.

use of GROUP BY
query nesting, also known as subqueries. You can think of subqueries as virtual tables.
(advanced) GROUP_CONCAT.

Statistics With Tcl And Mysql

1 Answers