Skip to content

Example

Jump to bottom
Noboru Saito edited this page Oct 19, 2019 · 3 revisions

Clarity of command output

Ignore the space before and after the delimiter. So, it can be used in the shaping of the output of the command.

$ ps |trdsql -oat -ih -id " " "SELECT * FROM -"
+-------+-------+----------+--------+
|  PID  |  TTY  |   TIME   |  CMD   |
+-------+-------+----------+--------+
| 11704 | pts/2 | 00:00:00 | ps     |
| 11705 | pts/2 | 00:00:00 | trdsql |
| 23171 | pts/2 | 00:00:27 | zsh    |
+-------+-------+----------+--------+

Add header

You can give another name by AS. If you want to change the output name.

trdsql -ojson "SELECT c1 as id ,c2 as name FROM test.csv"
[
  {
    "id": "1",
    "name": "Orange"
  },
  {
    "id": "2",
    "name": "Melon"
  },
  {
    "id": "3",
    "name": "Apple"
  }
]

When you want to name a table column. Add item name to CSV without header line.

$ cat <(echo id,name) test.csv|trdsql -ojson -ih "SELECT * FROM  -"
[
  {
    "id": "1",
    "name": "Orange"
  },
  {
    "id": "2",
    "name": "Melon"
  },
  {
    "id": "3",
    "name": "Apple"
  }
]

LTSV Log SQL

LTSV fromat

+-------------+------+
| column name | type |
+-------------+------+
| time        | text |
| method      | text |
| uri         | text |
| status      | text |
| size        | text |
| apptime     | text |
+-------------+------+

SQL

SELECT CASE INSTR(uri, '?')
            WHEN 0 THEN uri 
            ELSE SUBSTR(uri, 0, INSTR(uri, '?'))
        END AS url,
       method, 
       status, 
       count(uri),
       round(min(apptime),3) as min,
       round(max(apptime),3) as max,
       round(sum(apptime),3) as sum,
       round(avg(apptime),3) as avg,
       min(size),
       max(size),
       sum(size),
       avg(size)
  FROM log.ltsv
 WHERE apptime != '-'
 GROUP BY url,method
Clone this wiki locally