Product literature

To see if we can get valuable information from the literature, we can use the PostgreSQL text search features functionality.

We can gather statistics about the most used words. We use "english" as the dictionary, this will remove common words like "the", "and", "is" from the results.

SELECT word, ndoc
   FROM ts_stat($$
     SELECT to_tsvector('english', literature)
     FROM import.product_literature AS t
  $$)
  where LENGTH(word) > 4
  order by
    ndoc desc,
    LENGTH(word) desc
  limit 10;

(10 rows)

We can also search for literature references containing one ore more specific words, f.i. "covid". let's do that and find the 10 most referenced literature with the number of reports referencing them. As we will see, it is hard to detect any pattern, but there are more advanced ways to seek through text in PostgreSQL and we encourage everyone that is seriously interested in literature used in reports to look at the PostgreSQL documentation on text search.

select
  trim(literature),
  COUNT(DISTINCT local_number)
from import.product_literature
where
  to_tsvector('simple', literature) @@ to_tsquery('simple', 'covid')
GROUP BY trim(literature)
order by COUNT(DISTINCT local_number) desc limit 10;