Cluster Arrays

Working with Cluster Arrays

A cluster array in Sheerpower is a powerful data structure that functions like an in-memory spreadsheet, containing rows and columns of data. They are ideal for handling collections of related information efficiently. Like vectors in other languages, cluster arrays do not require preallocation—they grow automatically as needed. Sheerpower provides a rich set of built-in features for working with this data, allowing operations such as sorting, filtering, and searching, much like you would in a database.

With enough RAM and pagefile space, cluster arrays can support up to one billion rows with key lookup speeds of millions per second.

1. Defining and Populating a Cluster Array

First, you define the structure of your cluster. Then, you can add records (rows) to it using the add cluster statement.

2. Understanding the "Current Row"

Sheerpower keeps track of a current row within the cluster array. When you add a new record, that new record automatically becomes the current row. To work with a different record, you must first make it current using the set cluster statement.

3. Clusters Accessed as Arrays and as Dictionaries

A cluster field followed by a subscript in parentheses reads or writes one row without moving the current row. A number picks a row, like an array; a key — a string, or field: value — looks a row up, like a dictionary. Reach for it when you want one value from one row; to visit many rows, collect and for each remain the tools, and findrow when you want the found row to become current.

As an array: any row by number

Sometimes you just want to look at another row — the previous one, the last one, row 7 — without disturbing the current row. Put the row number in parentheses after the field name: inventory->product_name$(2) reads row 2's product name, and the current row stays exactly where it was. The row number is the physical row number, the same one set cluster and size() use; (*) means the last row.

The output:

As a dictionary: any row by key

A string subscript looks the row up by the cluster's lookup field. By default that is the first string field declared — here item_id$ — and the match is case-insensitive. If no key matches, a 0 or "" is returned. In addition _integer is set to zero so you can test for no matches if you need to.

To look up by a different field, name it in front of the key, as field: key. Any field will do — a string field, or a numeric one (with the field named there is nothing to confuse with a row number, so quantity: 75 is a lookup, not row 75). Write the field name exactly as it was declared, including its $ or ? ending if it has one.

A string lookup is case-insensitive. To require an exact-case match on a field, say so once with set on that field; it applies whenever that field is looked up, and nocase turns it back.

Writing by row or by key

The same subscripts work on the left of =: a number writes that row, a key (with or without a named field) writes the row that matches. The current row is untouched, and _integer is the row written.

Writing by key is strict on purpose. A key that matches no row raises a catchable lookupmiss exception — a misspelled key is far more likely than an intent to add a record, and silently adding one would surface weeks later as a phantom row in a report. When you do want “add it if it isn't there”, announce it with create: the row is added with the lookup field set to the key, and the field written. That makes a word count a one-liner:

Likewise, a key that matches several rows raises lookupdup — writing “the first one” would leave the others stale. If the first match is genuinely what you want (a “current” row per key), allow it with set inventory->item_id$: duplicates. Reads are always the first match.

4. Updating and Deleting Records

Modifying a record is simple: first make the record current, then assign a new value to its field.

Deleting records is also straightforward. For a detailed guide on removing rows, see Reset Cluster.

5. Iterating Over Records: The `collect` and `for each` Pattern

To process every record in a cluster array, you use a two-step pattern. This design is powerful because it separates the act of gathering/filtering data from the act of processing it.

1. collect ... end collect: This block builds a temporary collection (or view) of your data. This is where you do all your filtering (include/exclude) and sorting.
2. for each ... next: This block then loops through the temporary collection you just created.

Sorting by an Expression

The sort key does not have to be a field — it can be any expression, evaluated once per row. That matters when the interesting order lives in a computed value no single field holds. Here an inventory is ranked by the total value tied up in each item — price times quantity on hand:

The output:

Notice that Gizmo leads even though it is neither the most expensive item (that is Gadget) nor the largest quantity (that is Widget) — only the computed value reveals where the money actually sits. The expression decides the order and nothing more: no field in the cluster changes, and nothing is stored.

Conditional Sorting - a Different Sort Key per Row

The sort by statement runs once for each collected row, so it can live inside an if and supply a different expression for different rows. A classic use is a fallback key: sort clients by their home zipcode, but for clients with no home zipcode on file, use their billing zipcode instead:

The output:

Every row contributed exactly one key value — whichever zipcode its branch chose — and the rows came out in a single unified zipcode order: 02134, 30301, 60601, 98101.

The one rule: every branch must agree on each sort key's data type, direction, and case sensitivity, and supply the same number of sort keys. Sorting by a string in one branch and a number in another raises a catchable INCONSORT exception (−4112); changing the number of sort keys between rows raises BADSORT (−4111).

Statement order inside collect

Statement order inside collect is program order: the statements run top to bottom, once for each row. When an include fails (or an exclude matches), execution of the block stops for that row — statements below it, including sort by, never run for the rejected row, and it contributes no sort key — and therefore never counts toward groupmin or groupmax. So placing include/exclude before sort by skips the key-building work for rows that will be discarded. Filters may appear anywhere in the block — before, between, or after the sort keys — and the result is the same; filtering first simply does less work.

groupmin - Finding Duplicates

The groupmin n attribute is applied after the sort completes: it keeps only groups — rows that are equal on the complete set of sort keys — containing at least n rows. Its most common use is the classic data-cleanup question: which values appear more than once?

Here a contact list is checked for duplicate email addresses. Sorting by email brings equal addresses together; groupmin 2 then keeps only the addresses that occur at least twice — every row of every duplicated address, with the singletons gone:

The output:

Each duplicated address arrives with all of its rows, grouped together in sorted order and keeping the order they were added in — exactly what is needed to review or merge the duplicates. The cluster itself is untouched: size() still reports all seven rows.

groupmax n asks the opposite question of the same sort: keep only the groups with at most n rows. groupmax 1 on the same contact list returns the addresses that occur exactly once — the complement of the duplicate report:

The output:

The two compose: groupmin 2 groupmax 2 keeps exactly the pairs (here, only the two Diaz rows). A natural real-world use of groupmax 1 is reconciliation: load two sources into one cluster with a field marking which source each row came from, sort by the matching key, and groupmax 1 hands you every key that appears in only one source — the orphans — while groupmin 2 on the same sort gives the matched keys. One sort, both reports.

Points worth knowing:

• groupmin is a collection attribute, written after the colon like cachesize; the two can be combined (collect cluster c: cachesize 4000 groupmin 2).
• A group is defined by the complete set of sort keys, so sorting by two keys finds rows duplicated on the pair. Under sort nocase by, values differing only in case count as one group.
• When groupmin or groupmax is used, the sort runs immediately at end collect (instead of being deferred), so _extracted is the filtered row count right away.
• groupmin 1 keeps everything; a value below 1 raises a catchable GROUPBAD exception (−4114), and groupmin without any sort by is a compile error (GROUPNOSORT, −4113) — groups are defined by the sort keys.
• groupmax n is the mirror image: it keeps only groups with at most n rows. groupmax 1 answers the opposite question — which values appear exactly once? — and the two compose: collect cluster c: groupmin 2 groupmax 2 keeps exactly the pairs. The same rules apply (sort required, values below 1 raise GROUPBAD), plus one more: a groupmax below the groupmin could never keep a group, so it also raises GROUPBAD.

groupmin is the natural partner of unique (next section): unique answers “what are the distinct values?” with one row per value, while groupmin 2 answers “which values repeat?” with every row of every repeated value.

Fuzzy Selection - Scoring Rows

Because include and sort by are ordinary executable statements, they can work with values your own code computes as each row goes by. That turns a collect block into a scoring model: award points for each desirable trait, keep the rows that score high enough, and rank the survivors best-first. Here house listings are scored on bedrooms and lot position:

The output:

Each row is scored fresh (score = 0 runs once per row), include keeps only rows reaching the threshold, and sort descending by score presents the best matches first. 12 Elm St swept every trait (135 points). But look at 48 Oak Ave: a one-bedroom that qualified at 65 points on its garage, price, and size — exactly the near-miss a rigid “must have 2+ bedrooms AND ...” filter would have discarded, and it outranks the two-bedroom at 7 Pine Rd. That is what makes the selection fuzzy: no single condition is required, the traits just have to add up.

Notice too that the model reads budget — an ordinary variable set before the block — so the same scoring code serves any buyer. The weights are ordinary numbers your code controls: refining the model is editing a few if lines, not restructuring a query.

And because every stats$*() function accepts an optional trailing true meaning “over the collection”, the matches can be analyzed as a group without another pass:

See Cluster Statistics for the full rules of the collection flag.

unique - One Row per Distinct Value

Use unique with collect cluster to create a collected view containing one row for each distinct value of a field.

For each distinct field value, the first matching row in cluster file order becomes the representative row for that group.

The underlying cluster is not changed. The size() function still returns the total number of rows in the cluster, including duplicates. To obtain the number of distinct values, use _extracted after the end collect statement.

How unique Finds Distinct Values

Distinct values and their occurrence counts come directly from the field's hash index. As rows are added to the cluster, the index maintains a count for each indexed value.

Because this information already exists, unique does not need to scan the cluster or count duplicate rows at collect time. Its work does not increase merely because a value occurs many times.

Inside the collect cluster block, _extracted contains the occurrence count for the current group. It tells how many cluster rows have the current distinct field value.

After end collect, _extracted contains the number of distinct values retained in the collected view.

Creating a Word-Frequency View

The following example creates one collected row for each distinct word and stores the word's frequency in the representative row:

Inside the block, _extracted is the number of times the current word occurs. After the block, it is the number of distinct words in the collected view.

That single block performs the equivalent of a GROUP BY operation together with a COUNT for every group.

Filtering Groups

Unique grouping takes place before include and exclude expressions are evaluated. With unique, the block runs once per distinct value, with that group's representative row current; statement order within each pass works as described above.

Each expression is evaluated once for each distinct value, using its representative row. The group's complete occurrence count is available as _extracted. This makes group-level filtering a one-line operation.

For example, the following collection retains one representative row for every city name that occurs more than once:

After end collect, the collected view contains one row for each repeated city name. At that position, _extracted contains the number of repeated city names retained in the view.

6. Built-in Functions for Instant Data Aggregation and Statistics

Sheerpower provides built-in functions that can instantly calculate aggregate values across an entire cluster array field, eliminating the need for manual loops.

7. High-Speed Searching with `findrow()`

The findrow() function is highly optimized to perform millions of lookups per second. It returns the row number of the first record that matches your search criteria and makes the row current, or 0 if no match is found. The findrow() function works with any Sheerpower data type.