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/09/25 01:30] – daniel | sql_analysis [2016/10/04 16:17] – [Duplicate messages] daniel | ||
---|---|---|---|
Line 8: | Line 8: | ||
FROM header, (values (' | FROM header, (values (' | ||
WHERE position(E' | WHERE position(E' | ||
- | group by split_part(substr(lines, | + | group by 1 |
</ | </ | ||
Line 48: | Line 48: | ||
| | ||
</ | </ | ||
+ | |||
+ | This alternative implementation uses regular expressions and differs in that it doesn' | ||
+ | <code sql> | ||
+ | select FIELD||':' | ||
+ | from (select FIELD, regexp_matches(lines, | ||
+ | from header, | ||
+ | | ||
+ | | ||
+ | where strpos(lines, | ||
+ | GROUP BY 1 ORDER BY 1 | ||
+ | </ | ||
+ | |||
+ | In this version, the '' | ||
====== Duplicate messages ====== | ====== 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. | 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. | ||
Line 53: | Line 66: | ||
select h1.mail_id from header h1, header h2 where h1.lines=h2.lines and h1.mail_id > h2.mail_id | select h1.mail_id from header h1, header h2 where h1.lines=h2.lines and h1.mail_id > h2.mail_id | ||
</ | </ | ||
+ | |||
+ | A stricter version, comparing the md5 hashes of bodies (text and html parts) in addition to the headers: | ||
+ | <code sql> | ||
+ | select b2.mail_id from body b1, body b2, header h1, header h2 | ||
+ | where b1.mail_id < b2.mail_id | ||
+ | and h1.mail_id = b1.mail_id | ||
+ | and h2.mail_id = b2.mail_id | ||
+ | and md5(h1.lines) = md5(h2.lines) | ||
+ | and md5(b1.bodytext) is not distinct from md5(b2.bodytext) | ||
+ | and md5(b1.bodyhtml) is not distinct from md5(b2.bodyhtml); | ||
+ | </ | ||
+ | |||
+ | The '' | ||
+ | to the simple equality operator, for which '' | ||
+ | ====== 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