sql_analysis
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionNext revisionBoth sides next revision | ||
sql_analysis [2010/08/20 08:26] – daniel | sql_analysis [2012/01/28 02:55] – daniel | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | | + | |
This query extracts, for each of the header fields from a fixed list, the number of occurrences of all distinct values of the field. | This query extracts, for each of the header fields from a fixed list, the number of occurrences of all distinct values of the field. | ||
Line 10: | Line 10: | ||
group by split_part(substr(lines, | group by split_part(substr(lines, | ||
</ | </ | ||
+ | |||
+ | Sample output: | ||
+ | < | ||
+ | ct | count | ||
+ | ---------------------------+------- | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | </ | ||
+ | ====== Duplicate messages ====== | ||
+ | This query finds each message that share the exact same headers than another message with a lower mail_id, which means that it's a duplicate. | ||
+ | <code sql> | ||
+ | select h1.mail_id from header h1, header h2 where h1.lines=h2.lines and h1.mail_id > h2.mail_id | ||
+ | </ | ||
+ | |||
+ | ====== Messages with specific attachment types ====== | ||
+ | To retrieve all messages containing pdf files or any image file: | ||
+ | <code sql> | ||
+ | select distinct mail_id FROM attachments WHERE content_type=' | ||
+ | </ | ||
+ | |||
+ | ====== Messages sent or received today ====== | ||
+ | < | ||
+ | select mail_id from mail where msg_date> | ||
+ | </ | ||
+ | |||
sql_analysis.txt · Last modified: 2019/01/24 17:55 by daniel