soeretypo: extraction of product statistics

Registered by Olivier Maury on 2012-03-21

After discussion with Sabine, one of the first extraction she's waiting is statistics on products:
by product types:
- products total
- number of products where is studied:
  - mineralisation
  - biochemical/chemical composition
  - spectra
  - raw materials are known
  - treatments are known

AM (2012/04/16):
To complete what Olivier said, Sabine wants a global statistic extraction which could be placed into the interface menu just after the "PRO informations". She wants to extract statistics with selecting data types (mineralization, composition, IR measurements) and PRO types.

Blueprint information

Status:
Not started
Approver:
amd
Priority:
Undefined
Drafter:
amd
Direction:
Needs approval
Assignee:
Nautile bleu
Definition:
Discussion
Series goal:
None
Implementation:
Unknown
Milestone target:
None

Related branches

Sprints

Whiteboard

Goulwen: I've started to work on this new query. I'm not sure to full understand the request, so let me try to reformulate it:

The goal is to be able to view which how many products have been tested for the mineralization, the (bio)chemical composition and the IR spectrum, grouped by products types.

For testing that there's data for the mineralization, we look for results in the `mineralization` table.
For testing that there's data for the (bio)chemical composition, we look for results in the `composition` table.
For testing that there's data for the IR spectrum, we look for results in the `spectre` table.

For the mineralization, this leads for some difficulties:

* There's most of time 2 incubations by product, 1 for Carbon, 1 for Nitrogen, so the number of result is multiplied by 2 (but we can't be sure that there's always 2 incubations by product, if I remember correctly)
* The mineralization is followed on several days, which also leads to multiplied the number of products.

Is it possible to limit joins to the `echantillon` table and to consider that if there's a record in this table, we will have at least one result on the `mineralization` table?

The first draft of the query is available below:

SELECT
 DISTINCT
  tpdt_nom,
  COUNT(pdt_cn) AS nb_pdt,
  (
   SELECT COUNT(*)
   FROM type_produit type_produit_
   INNER JOIN produit produit_
    ON produit_.pdt_tpdt_cn = type_produit_.tpdt_cn
    INNER JOIN prelevement
     ON prv_pdt_cn = produit_.pdt_cn
      INNER JOIN echantillon
       ON ech_prv_cn = prv_cn
       INNER JOIN incubation
        ON inc_ech_cn = ech_cn
   WHERE
    type_produit_.tpdt_nom = type_produit.tpdt_nom

   GROUP BY
    type_produit_.tpdt_nom
   ORDER BY
    type_produit_.tpdt_nom

  ) AS nb_pdt_min,
  (
   SELECT COUNT(*)
   FROM type_produit type_produit_
   INNER JOIN produit produit_
    ON produit_.pdt_tpdt_cn = type_produit_.tpdt_cn
    INNER JOIN composition
     ON cmp_pdt_cn = produit_.pdt_cn
     INNER JOIN matiere_premiere
      ON mpr_cn = cmp_mpr_cn
   WHERE
    type_produit_.tpdt_nom = type_produit.tpdt_nom

   GROUP BY
    type_produit_.tpdt_nom
   ORDER BY
    type_produit_.tpdt_nom

  ) AS nb_pdt_compbio,
  (
   SELECT COUNT(*)
   FROM type_produit type_produit_
   INNER JOIN produit produit_
    ON produit_.pdt_tpdt_cn = type_produit_.tpdt_cn
    INNER JOIN prelevement
     ON prv_pdt_cn = produit_.pdt_cn
     INNER JOIN echantillon
      ON ech_prv_cn = prv_cn
      INNER JOIN spectre
       ON spe_ech_cn = ech_cn
   WHERE
    type_produit_.tpdt_nom = type_produit.tpdt_nom

   GROUP BY
    type_produit_.tpdt_nom
   ORDER BY
    type_produit_.tpdt_nom

  ) AS nb_pdt_spectre
FROM type_produit
INNER JOIN produit
 ON pdt_tpdt_cn = tpdt_cn
GROUP BY
 tpdt_nom
ORDER BY
 tpdt_nom

(?)

Work Items

This blueprint contains Public information 
Everyone can see this information.

Subscribers

No subscribers.